Cross Reference Count Formula with Multiple Criteria

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, " "})


Best Answer


  • 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)

  • 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.

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    That worked!


    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!