COUNTIF or COUNTIFS

Rofonic27

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.  

L_123

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

 

 

L_123

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.

Rofonic27

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.  

Paul Newcome

=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...

Rofonic27

Got it.  Thanks to both of you.