How to use the COUNTIF formula when you have an OR statement that looks at multiple columns
I am trying to come up with a formula that will calculate how many tasks I have that are either "At Risk", which is a checkbox (flag) column, or has red health, which is a symbol column. I need a formula that does not add the totals of those conditions. For example, if a task is both At Risk and red health, I want that to be counted as 1 instead of 2. The formula I initially started with is:
=COUNTIF([Task Name]:[Task Name], OR([At Risk]:[At Risk] = true, Health:Health = "Red"))
I have tried different COUNTIFS formulas and even tried to use [At Risk]@row and Health@row and it still gave me error each time.
I thought this would be a simple formula, but of course, it isn't.
Best Answer
-
You would have to COUTNIFS flags, add that to COUNTIFS "Red" then subtract from the the COUNTIFS both.
=COUNTIFS([At Risk]:[At Risk], @cell = 1) + COUNTIFS(Health:Health, @cell = "Red") - COUNTIFS([At Risk]:[At Risk], @cell = 1, Health:Health, @cell = "Red")
Answers
-
You would have to COUTNIFS flags, add that to COUNTIFS "Red" then subtract from the the COUNTIFS both.
=COUNTIFS([At Risk]:[At Risk], @cell = 1) + COUNTIFS(Health:Health, @cell = "Red") - COUNTIFS([At Risk]:[At Risk], @cell = 1, Health:Health, @cell = "Red")
-
Thanks @Paul Newcome. This worked perfectly.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!