# Help on formula: count if with multiple criteria

Options
edited 12/09/19

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

Tags:

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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")

• Options

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

• ✭✭✭✭✭✭
Options

You're welcome!

• Options

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

• Options

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

• ✭✭✭✭✭✭
Options

@Sean McNulty Try something like this...

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

• Options

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

Comes up with #Contact Expected ?

Are you able to assist

Cheers

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

@Nancy Heater Try this...

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

• Options

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)

• ✭✭✭✭✭✭
Options

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)

• Options

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

• Options

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!