Countifs and Or Function


I am attempting to use the countifs function to assess how many requests are in a critical status, but are also not completed, canceled, or otherwise not active.

Those statuses are: "Active Sprint", "Backlog", "On Hold", "Sprint Assigned", "New".

This formula works and is referencing the "Critical" requests that are in "Active Sprint".

=COUNTIFS({Business Unit}, $[Business Unit]@row, {Jira Priority Level}, [Priority Level - Critical]$1, {Request Status}, CONTAINS("Active Sprint", @cell))

As soon as I try to add multiple statuses from the other sheet: ("Active Sprint", "Backlog", "On Hold", "Sprint Assigned", "New") the formula doesn't give me an error, but it gives me a zero. Where as the first furmula currently has 2. I was expecting any other "critical" requests to add to that sum.

=COUNTIFS({Business Unit}, $[Business Unit]@row, {Jira Priority Level}, [Priority Level - Critical]$1, AND({Request Status}, CONTAINS("Active Sprint", @cell), OR({Request Status}, CONTAINS("Backlog", @cell), OR({Request Status}, CONTAINS("Sprint Assigned", @cell), OR({Request Status}, CONTAINS("On Hold", @cell))))))

How can I get the formula to add ANY of those request statuses to the total?


Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    You do not need the AND before Request Status - AND is already assumed in the COUNTIFS.

    The syntax for the OR part isn't quite right. You just need one OR with all the options.

    =COUNTIFS({Business Unit}, $[Business Unit]@row, {Jira Priority Level}, [Priority Level - Critical]$1,{Request Status}, OR(CONTAINS("Active Sprint", @cell), CONTAINS("Backlog", @cell), CONTAINS("On Hold", @cell)_______ ))

    Continue to add more CONTAINS into the gap


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!