Help on formula: count if with multiple criteria

Grace
Grace
edited 12/09/19 in Formulas and Functions

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

count.png

Best Answer

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    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. 

     

«13456

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

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

  • 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
    Mike Wilday ✭✭✭✭✭✭

    You're welcome! :) 

  • 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

  • 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
    Paul Newcome ✭✭✭✭✭✭

    @Sean McNulty Try something like this...

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

  • 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
    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
    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
    Paul Newcome ✭✭✭✭✭✭

    @Nancy Heater Try this...

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

  • @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
    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)

  • @Paul Newcome

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

  • @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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!