COUNTIF or COUNTIFS
I think I have formulated myself into a corner. What I am attempting to do is create a total number of all open projects based on the multiple status options in 5 separate columns. So, if the data in columns Stats1:Stats5 match X or Y or X, return that total count.
I am able to do this for One status... =COUNTIF([Status1]:[Status5], "Complete") . I need it to look for multiple statuses. And although this works for the one status, it appears to be looking at the other columns in between Status1:Status5. Which is not what I intended. I Only want to be looking at columns Status1, Status2, etc.
1) How do I write this to Only look in specific columns; 2) How do I write this to look for the multiple status options?
Any help would be greatly appreciated.
Comments
-
Your issue is your sheet design. Instead of putting status2 to the right, you should put it under status 1, and use the same row. The fewer columns you use the better.
That said if you really want to keep the design your going to have to add each countif and do one for each status.
=COUNTIF([Status1]:[Status1], "Complete")+COUNTIF([Status2]:[Status2], "Complete") etc
-
mark that. Found a way to do it.
=COUNT(COLLECT([Status1]:[Status5],[Status1]:[Status5] , @cell = "Complete"))
Still this isn't really optimal as it calculates on a bunch of cells it doesn't need to, and if another cell says complete anywhere in that range it will count it in the formula.
-
Thanks for the response. I'll try that out.
It would seem the design is off, but column Status 2 is not the same as the criteria for the Statuses. It is a second column that refers to Statuses in a separate sheet. This is a master roll up sheet used to feed a dashboard. So Status1:Status5 represent the status columns of sheets 1-5.
And I guess I should clarify, I am not looking to just collect Status Complete. I am looking to collect Complete, In Progress, On Hold, In Testing (Etc) from Status1:Status5 in a single count. So how many of those matching criteria are in Columns Status1:Status5.
-
=COUNTIF([Status1]:[Status5], OR(@cell = "Complete", @cell = "In Progress", @cell = "On Hold", etc...))
Similar to Luke's but skips out on the COLLECT part and combines all of the various dropdown options. If you didn't want to include the other columns in between the statuses you would have to break it down to
=COUNTIF([Status1]:[Status1], OR(@cell = "Complete", @cell = "In Progress", @cell = "On Hold", etc...)) +
COUNTIF([Status2]:[Status2], OR(@cell = "Complete", @cell = "In Progress", @cell = "On Hold", etc...)) + etc...
-
Got it. Thanks to both of you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.5K Get Help
- 63 Global Discussions
- 46 Industry Talk
- 387 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 466 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!
