Countifs with or
I need some help with my formula:
I like to show the number of open Risks OR Issues that are <> Closed. I do have 1 open risk in my issue log. When I use the formula without the "OR" I get the correct number for my 1 risk. When I add the "OR", I get the number "0". It should be 1.
This formula returns correct 1
=IFERROR(COUNTIFS({03. RAID Log | SMP2024-0022 RAID Status}, <>"Closed", {03. RAID Log | SMP2024-0022 Raid Type}, "Risk"), 0)
This formula returns incorrect 0
=IFERROR(COUNTIFS({03. RAID Log | SMP2024-0022 RAID Status}, <>"Closed", OR({03. RAID Log | SMP2024-0022 Raid Type}, "Risk", {03. RAID Log | SMP2024-0022 Raid Type}, "Issue")), 0)
SYLVIA KAY | DIRECTOR PROJECT MANAGEMENT
PLATFORM ENGINEERING & TECHNOLOGY TEAM
AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL
M: 404-664- 1025 | SYLVIA.KAY@AMEXGBT.COM
follow us on twitter: @AmexGBT
follow us on instagram: @AmexGBT
Answers
-
The reason this is returning 0 is due to an error in the formula. To troubleshoot situations like this remove the IFERROR portion so you can see the error code. How ever in this situation. IT looks like it is a syntax error. OR statements syntax is the full logical expression. This causes other problems with how the COUNTIFS works. Effectively COUNTIFS look at a range first then the expression separated by a coma. Also COUNTIFS have an understood AND wrapped with in the function. the OR statement makes the entire bit with in it a statement and not a range. THUS to get this to work you would need.
=IFERROR(COUNTIFS({03. RAID Log | SMP2024-0022 RAID Status}, <>"Closed", {03. RAID Log | SMP2024-0022 Raid Type}, "Risk"), 0) + IFERROR(COUNTIFS({03. RAID Log | SMP2024-0022 RAID Status}, <>"Closed", {03. RAID Log | SMP2024-0022 Raid Type}, "Issue"), 0)
I hope my explanation is understandable. I could fix problems all day long but some times struggle on how to explain why something is a problem.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!