COUNTIFS Contains but Not Contain

Nick Allgauer

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

Kelly Moore

Hi Nick

Try using the HAS function instead

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

Kelly Moore

Hi Nick

Try using the HAS function instead

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

Nick Allgauer

Thank you! That did it!

SRenner

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

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

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

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

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