Count if contains and if not contains in the same cell

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.

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.