COUNTIFS Contains but Not Contain

Options

I have a multi-select column that contains both the options Pending and Pending Registration. I want to count if the Contract Status column contains Pending but not Registration. Here's what I have so far... need help.


=COUNTIFS({Contract Status}, CONTAINS("Pending", @cell), ({Contract Status}, NOT(CONTAINS("Registration", @cell))))

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 01/16/21 Answer ✓
    Options

    Hi Nick

    Try using the HAS function instead

    =COUNTIFS({Contract Status}, HAS(@cell, "Pending"), {Contract Status}, NOT(HAS(@cell, "Registration")))

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 01/16/21 Answer ✓
    Options

    Hi Nick

    Try using the HAS function instead

    =COUNTIFS({Contract Status}, HAS(@cell, "Pending"), {Contract Status}, NOT(HAS(@cell, "Registration")))

  • Nick Allgauer
    Nick Allgauer ✭✭✭✭
    Options

    Thank you! That did it!

  • SRenner
    SRenner ✭✭✭
    Options

    Hello I have a side question to this.

    I have a column that has SPAP, SPEP, SPDE, and SPFL. I want to count the first three but not the SPFL. is there a formula for this?


    thanks

    Scott

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 01/18/21
    Options

    Hi Scott

    I would still use the COUNTIFS and HAS functions.

    COUNTIFS uses the syntax of (range1, criteria1, range2, criteria2, range3, criteria3, etc). You can use COUNTIFS even if you only have single range and criteria.

    Are you counting on the same sheet or from a different sheet? The formula below is for using a different sheet

    =COUNTIFS({Your cross sheet referenced column}, NOT(HAS(@cell, "SPFL")))

    you may have to add the criteria of non-blanks to the above- let me know.

    If from the same sheet:

    =COUNTIFS(your column:your column, NOT(HAS(@cell, "SPFL")), your column:your column, <>"")

    Note: This will count any cell within that range that does not have SPFL as it's value. If there are other choices that you want excluded, we need to make provisions for that.

  • SRenner
    SRenner ✭✭✭
    Options

    YES! I used the formula below on a different sheet and you are correct that I need help with the non blanks. its counting about 9-10 more than it should because of them.

    =COUNTIFS({Your cross sheet referenced column}, NOT(HAS(@cell, "SPFL")))

    you may have to add the criteria of non-blanks to the above- let me know.



    Thank you

    Scott

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Try this

    =COUNTIFS({Your cross sheet referenced column}, NOT(HAS(@cell, "SPFL")), {Your cross sheet referenced column}, @cell <> "")

    This adds the criteria that the cell is not blank

  • SRenner
    SRenner ✭✭✭
    Options

    That did the trick!!! thank you for all the help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!