Count if contains and if not contains in the same cell

Tzippy ✭✭
edited 02/06/23 in Formulas and Functions

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
    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! 😊

  • @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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!