Count if contains and if not contains in the same cell

Tzippy

I need to count how many cells there are in a column IF that cell contains one thing AND does not contain another thing.

I can do one or another but having trouble combining it.

So far I have this and it works - =COUNTIFS({Master Project}, NOT(CONTAINS("HHA", @cell)))

It returns all cells which contain "HHA". Now I need to add in addition to containing "HHA" it should EXCLUDE those that contain "PCA"

I hope someone can help. I've been searching for answers in the Smartsheet community but haven't had any luck.

Nick Korna

Hi @Tzippy ,

I think your formula would count the cells in the range that do not contain "HHA". To get a count of cells with "HHA" and excluding "PCA" this should work:

=COUNTIFS({Master Project}, CONTAINS("HHA", @cell), {Master Project}, NOT(CONTAINS("PCA", @cell)))

Hope this helps; if you've any questions then just ask! 😊

Tzippy

@Nick Korna

Thank you Nick, that worked.

Now I'm having an issue adding a third step. I want to add that it should count all cells that have a number 1 in another column on that row. (in addition to the other row having HHA and not PCA as the formula mentioned above)

Thank you

Nick Korna

@Tzippy ,

You would just need to add another section to the COUNTIFS formula. If the column contains just numbers then it would be something along the lines of:

=COUNTIFS({Master Project}, CONTAINS("HHA", @cell), {Master Project}, NOT(CONTAINS("PCA", @cell)), {Number Column Ref},1)

Obviously you can have a different reference but you should be able to get the gist of this.

If the column with the 1/not 1 in it also has other text in it (as part of a text chain) then you can use CONTAINS similar to the other sections of the formula.