Cross Reference Count Formula with Multiple Criteria

08/06/20
Answered - Pending Review

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

Answers

  • L@123[email protected] ✭✭✭✭✭

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

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

  • 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

  • L@123[email protected] ✭✭✭✭✭

    It is in the popup where you select the range. At the top of it you can edit what the range is called

Sign In or Register to comment.