Countifs and Or Function

Justin Miller

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?

KPH

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

KPH

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

Justin Miller

This worked! Thank you!

KPH

Happy to help @Justin Miller