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!
Best Answer
-
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)))
Answers
-
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)))
-
@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!
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!