Cross Reference Count Formula with Multiple Criteria

Anthony Barthelemy
Anthony Barthelemy ✭✭✭✭✭✭
in Formulas and Functions

Smartsheet Community:

I am trying to Count If the Checkbox is Checked, the Date is Less than August 5 or Blank. I know I am close but can not see finish the Formula Correctly. Current Formula Below:

=COUNTIFS({Issue / Defect UAT LOG Range 3}, "1", {Issue / Defect UAT LOG Range 4}, <DATE(2020, 8, 5), {Issue / Defect UAT LOG Range 4, " "})


Thanks

· Share on FacebookShare on Twitter

Best Answer

Answers

  • L_123
    L_123 ✭✭✭✭✭✭

    =COUNTIFS({Issue / Defect UAT LOG Range 3}, "1", {Issue / Defect UAT LOG Range 4}, or(@cell<DATE(2020, 8, 5),isblank(@cell)

    give this one a try. If this doesn't work double check your range references that they don't have an error inside their scope, and that they reference the same amount of cells.


    (Also you should always name your ranges as it makes it much easier to troubleshoot or modify, especially years down the road when you don't remember exactly what they do)

    · Share on FacebookShare on Twitter
  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    How do I name the Range? I did not know you could do that. I just click the entire Column to select the Range.

    · Share on FacebookShare on Twitter
  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    That worked!

    Thanks

    I will look into Naming my Ranges as I agree it would be helpful. If you can direct me to the link for the knowledge area that covers that, that would be great.


    Thanks again

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!