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
-
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
Answers
-
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
-
This worked! Thank you!
-
Happy to help @Justin Miller
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!