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

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…