Combining CONTAINS, with NOT(ISBLANK and NOT(CONTAINS

Hello Smartsheet Community!

We have (3) columns: Country, Tracking, Project Status.

We need to count the number of cells that contain "CAN" for Country, Tracking is "Not Blank", and Project Status does not contain the word "Complete".

Below is what we attempted, but are unable to get a result:

=COUNTIFS(Country:Country, CONTAINS("CAN", @cell)), Tracking:Tracking, NOT(ISBLANK(@cell)), [PROJECT STATUS]:[PROJECT STATUS], NOT(CONTAINS("COMPLETE"@cell))

Thanks in advance for the assistance!

Find more posts tagged with

Accepted answers

Kelly Moore

Hi @Nancy Heater

It looks like you're missing a comma in your very last term and had some parentheses in the wrong places.

=COUNTIFS(Country:Country, CONTAINS("CAN", @cell), Tracking:Tracking, NOT(ISBLANK(@cell)), [PROJECT STATUS]:[PROJECT STATUS], NOT(CONTAINS("COMPLETE", @cell)))

All comments

Kelly Moore

Hi @Nancy Heater

It looks like you're missing a comma in your very last term and had some parentheses in the wrong places.

=COUNTIFS(Country:Country, CONTAINS("CAN", @cell), Tracking:Tracking, NOT(ISBLANK(@cell)), [PROJECT STATUS]:[PROJECT STATUS], NOT(CONTAINS("COMPLETE", @cell)))

Nancy Heater

@Kelly Moore ... you have no idea how long I was starting at this before I gave up and posted it. Ugh! And I knew it was going to be something super simple too. I was so close!

Thank you so much for taking the time to help me out!

Kelly Moore

Hey @Nancy Heater

I'm glad we got it to work. That is always the most important thing in a formula.

I wanted to be sure you knew that we didn't have to always use the CONTAINS function when searching for text-forgive me for checking. If the responses are in regular dropdowns (not multiselect), you could possibly search for the text directly, thereby simplifying the number of functions needed, which always helps with those pesky parentheses.

For example, without the contains it could be written as:

=COUNTIFS(Country:Country, "CAN", Tracking:Tracking, <>"", [PROJECT STATUS]:[PROJECT STATUS], <>"Complete")

(the <> is another way of writing the NOT)

cheers

Kelly

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.