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.

Find more posts tagged with

Manufacturing

Formulas

Dashboards

IT & Operations

Account and User Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?