Help on formula: count if with multiple criteria

Help on formula: count if with multiple criteria

GraceGrace
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

Comments

  • Mike WildayMike 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 WildayMike 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 WildayMike 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 NewcomePaul 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 NewcomePaul 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.

Sign In or Register to comment.