Help on formula: count if with multiple criteria

Grace

Hi all,

I'm trying to get the count of active projects in my tracker sheet however I'm getting zero as a result. Please, can you check and advise what should be the correct formula?

Here's the formula I used:

=COUNTIFS(Stage:Stage, "planning", Stage:Stage, "strategy", Stage:Stage, "tender", Stage:Stage, "evaluation", Stage:Stage, "contract")

I only wanted to get the count in the highlighted cells in Stage column that has these 5 stages:

Planning

Strategy

Tender

Evaluation

Contract

Kind regards,

Grace

Mike Wilday

Hi Grace, When you use =Countifs and supply multiple criteria you are basically suggesting that you want to count each row that matches each of your criteria. In your formula, you are saying count all rows that have a "Stage and Planning and Strategy and Tender and Evaluation and Contract" in the row. None of your rows have all those answers in one row. To achieve a total count you will need to add separate =Countifs together like this. 

=Countifs(Stage:Stage, "planning") + Countifs(Stage:Stage, "strategy") + Countifs(Stage:Stage, "tender") + Countifs(Stage:Stage, "evaluation") + Countifs( Stage:Stage, "contract")

That should return the exact result you are looking for. 

 

Mike Wilday

Hi Grace, When you use =Countifs and supply multiple criteria you are basically suggesting that you want to count each row that matches each of your criteria. In your formula, you are saying count all rows that have a "Stage and Planning and Strategy and Tender and Evaluation and Contract" in the row. None of your rows have all those answers in one row. To achieve a total count you will need to add separate =Countifs together like this. 

=Countifs(Stage:Stage, "planning") + Countifs(Stage:Stage, "strategy") + Countifs(Stage:Stage, "tender") + Countifs(Stage:Stage, "evaluation") + Countifs( Stage:Stage, "contract")

That should return the exact result you are looking for. 

 

Mike Wilday

Use Countifs with multiple ranges and criterion to limit your results... Like show me all Contract Stages that are completed. 

=Countif(stage:stage, "Contract", Status:Status, "Completed")

Grace

Hi Mike,

Thank you for the quick response. The formula worked! and yes, I am already using a formula same as above for my completed projects. Thank you

Kind regards,

Grace

Mike Wilday

You're welcome!  

Grace

Hi Mike,

Trust all is well.

Following on this thread, please can you advise the correct formula for getting the count of projects that are active within a specific month? We would like to track the number of projects that are still active for 2018.

I'm trying the formula below but I'm getting an #INCORRECT ARGUMENT error.

=COUNTIFS(Stage:Stage, [Forecasted End Date]:[Forecasted End Date], IFERROR(AND(MONTH(@cell) <= 1, YEAR(@cell) = 2018), Status:Status, "active"))

The Stage headers that are active are called:

Planning

Strategy

Tender

Evaluation

Contract

Thanks in advance for the response.

Kind regards,

Grace

Sean McNulty

This is very similar to the issue I am having,

I can apply the =Countifs(Stage:Stage, "planning") to the column however it only picks up those cells that have a single value planning. I have some cells in that column that have multiple values and trying to extract the "Planning values: from these also to get a true total across the entire column of that value

Paul Newcome

@Sean McNulty Try something like this...

=COUNTIFS(Stage:Stage, CONTAINS("planning", @cell))

Sean McNulty

Thanks Paul,

What if I wanted to create a formula based on 2 columns of data.

1 column is regions & 1 column isTrades

I need to be able to show trades that are available in a regions identified on the regions columns so the formulas I have been trying is

=COUNTIFS(Regions:Regions, CONTAINS("Townsville", @cell), Trade:Trade, CONTAINS("Civil Works", @cell))

Comes up with #Contact Expected ?

Are you able to assist

Cheers

Paul Newcome

@Sean McNulty It sounds like you are putting the formula into a contact type column. Try either changing the column type to a text/number or moving the formula to a different column that is formatted as such.

Nancy Heater

What about counting different info in the same column ... example, I need to count all instances of "red" and all instances of "blue" in a column ... I tried the formula below but it is not returning the correct numbers:

=COUNTIFS([COLOR]:[COLOR], CONTAINS("red", @cell), [COLOR]:[COLOR], CONTAINS("blue", @cell))

Thank you!

Paul Newcome

@Nancy Heater Try this...

=COUNTIFS(Color:Color, OR(@cell = "red", @cell = "blue"))

Megan Harry

@Paul Newcome

Hi Paul!

I am trying to do something similar but can't seem to get the equation right. I need to count if how many times "Very Friendly" and "Friendly" is listed in my Friendliness column. I am also trying to find the entries within a specific date range. I also want to divide that total by the total count in the column.

Below is the equation I tried.. I know it isn't correct, so hopefully you can help me simplify this!

=COUNTIFS(Friendliness:Friendliness, CONTAINS("Very Friendly", @cell), Friendliness:Friendliness, CONTAINS("Friendly", @cell), AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31))) / COUNT(Friendliness:Friendliness)

Paul Newcome

Try this...

=COUNTIFS(Friendliness:Friendliness, OR(@cell = "Very Friendly", @cell = "Friendly"), [Date Column]:[Date Column], AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31))) / COUNT(Friendliness:Friendliness)

Megan Harry

@Paul Newcome

You are a life saver!! It worked perfectly! I am so thankful for the help!

Megan Harry

@Paul Newcome

Hi again! The formula you gave me did work perfectly, but I realized I explained what I want/need incorrectly. I don't want to divide those total counts by the entire column. I want to divide the counts by the total entries but only during that same date range. Is it possible to do what I am asking? Here is the formula I am trying to use but it is telling me incorrect argument set.

=COUNTIFS(Friendliness:Friendliness, OR(@cell = "Very Friendly", @cell = "Friendly"), Date:Date, AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31))) / COUNTIF(Friendliness:Friendliness, Date:Date, AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31)))

Thank you for the help!