COUNTIFS with multiple criteria

Options

Hi all,

I am looking to count a data range based on the criteria in two columns (yes, I'm British, hence the spelling!)

The fist column is a simple choice of three individual words, Ordered/Live/Complete. However, the second column has all our different contract types which I've narrowed down for the purposes of this question.

So, basically, I want a count of all the data that in column [STATUS] reads "Live" and that in column [CONTRACT TYPE] includes either "Cont" or "Affinity". I suppose at this point I should say that the [CONTRACT TYPRE] cells contain a lot more than the text I've offered up here but this is the best I can do in my attempts to make the statement as short as possible.

I would paste in what I've tried to construct but I think I'm confusing myself.

Thanks in advance.

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/27/21 Answer ✓
    Options

    @Martin Hughes

    I did the formula on the shared sheet to count using the 2 criteria "Cont" & "Affinity", Please check it.

    =COUNTIFS([CONTRACT TYPE]:[CONTRACT TYPE], OR(CONTAINS("Cont ", @cell), CONTAINS("Affinity", @cell)))

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/26/21
    Options

    Hi @Martin Hughes

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    For the criteria you mentioned in your question you can use the following formula:

    =IFERROR(COUNTIFS(STATUS:STATUS, @cell = "Live", [CONTRACT TYPE]:[CONTRACT TYPE], OR(@cell = "Cont", @cell = "Affinity")), "")

    the following screenshot shows the result


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Martin Hughes
    Options

    Sorry Bassam, please see above.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Martin Hughes

    Did you want the formula to count the (Status = Complete) and (CONTRACT TYPE contains "Contract hire") ?

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Martin Hughes
    Options

    @Bassam Khalil Almost.

    The CONTRACT TYPE column contains many more types, one where an entry is truncated to "Cont Hire xxxxx" xxxxx being variable text. Hence why just looking for "Cont" seemed to be the easiest way, along with another contract type referenced as "Affinity xxxxx"


    I'm trying to construct

    =COUNTIFS([CONTRACT STATUS]:[CONTRACT STATUS] = "Live", CONTAINS("Cont", [CONTRACT TYPE]:[CONTRACT TYPE]), CONTAINS("Affinity", [CONTRACT TYPE]:[CONTRACT TYPE]))

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Martin Hughes

    Please Try the following formula:

    =IFERROR(COUNTIFS([CONTRACT STATUS]:[CONTRACT STATUS], @cell = "Live", [CONTRACT TYPE]:[CONTRACT TYPE], OR(@cell = "Cont", @cell = "Affinity")), "")

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Martin Hughes
    Options

    @Bassam Khalil Your answer gives me a count of zero as I think your build column is including specific text.

    I would try and build a copy of the sheet for you but there's too many conversations to delete.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Martin Hughes 

    If you can share me as an admin on a copy of your sheet (after removing or replacing any sensitive information) i will create the exact formula for you.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/27/21 Answer ✓
    Options

    @Martin Hughes

    I did the formula on the shared sheet to count using the 2 criteria "Cont" & "Affinity", Please check it.

    =COUNTIFS([CONTRACT TYPE]:[CONTRACT TYPE], OR(CONTAINS("Cont ", @cell), CONTAINS("Affinity", @cell)))

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Martin Hughes
    Options

    @Bassam Khalil Thank you so much for this. The formula didn't count the word 'Contract' which I though it would by using just 'Cont' but now, seeing the formula, it's easy enough to add that in too.

    Thanks again for your time. It is appreciated.

    Martin

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Martin Hughes

    Excellent, and I will be happy to help you any time.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!