RAID Metrics
I'm trying to figure out how to build a formula using the COUNTIFS function that will count the number of ACTION items in our raid log but only if those items don't have one of our multiple closed status's. ONLY Active items if you will.
Current Formula: =COUNTIFS({RAID Type}, "Action") - this returns the correct count of action items but it includes Action items that are also closed. We have multiple closed status's and I don't want those included in the count.
Example Raid Types - Action, Risk, Issue, Decision, Question
Example Closed Status - Closed - Decision, Closed - Resolved
Best Answers
-
Sorry, the formula should be as shown below. I accidently used = sign instead of <>
=COUNTIFS({RAID Type}, "Action", {Status}, OR(@cell<>"Closed 1", @cell<>"Closed 2", @cell<>"Closed 3"))
-
No problem. Glad to help.
Answers
-
Use the OR() function within the COUNTIFS
=COUNTIFS({RAID Type}, "Action", {Status}, OR(@cell="Closed 1", @cell="Closed 2", @cell="Closed 3"))
You could also look at using =COUNT(COLLECT())
-
Let me try that, thank you very much!
-
Sorry, the formula should be as shown below. I accidently used = sign instead of <>
=COUNTIFS({RAID Type}, "Action", {Status}, OR(@cell<>"Closed 1", @cell<>"Closed 2", @cell<>"Closed 3"))
-
I'm getting an invalid reference error: =COUNTIFS({RAID Type}, "Action", {*Status}, OR(@cell = "Closed - Resolved", @cell = "Closed - Canceled", @cell = "Closed - Workday Case", @cell = "Closed - Decision", @cell = "Closed - Workday Brainstorm"))
-
Fixed it, the range reference was incorrect. Again, thank you.
-
Your {Status} reference has an asterisk in the reference name within the formula. Is that correct?
-
Well almost. The count is coming back as "1". there are actually 3 that are not closed.
-
Did you see my comment about changing the equal sign to <> ?
-
I think I figured it out. Tks
-
No problem. Glad to help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.8K Get Help
- 65 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.6K Ideas & Feature Requests
- 56 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!