Count if formula with multiple criteria where a field contains certain text
Hello,
I am having trouble writing a formula to count rows. Where row 1 = Yes where row2 does not contain "virtual" or "needed" (these single words are fragments of the selection in the drop-down list). This formula is on one sheet and counting values from a different sheet.
I can get this to work in reverse ie count row 1 if yes and count row 2 where row2 contains "Virtual" or "Needed" but I can't get it to work with the Not.
I have tried it both of the ways below.
=COUNTIFS({Summer Program}, "Yes", {On site desk 1}, OR(CONTAINS(NOT("Virtual", @cell), CONTAINS(NOT("needed", @cell)))))
=COUNTIFS({Summer Program}, "Yes", {On site desk 1}, OR(NOT(CONTAINS("Virtual", @cell), CONTAINS("needed", @cell))))
Answers
-
Hi @latheobald89201, I'm pretty sure if you try to put two "Not" statements together in an "Or", you will select everything (if you are pointing this at a single-select pulldown).
Try breaking them up:
=COUNTIFS({Summer Program}, "Yes",{On site desk 1}, NOT(CONTAINS("Virtual", @cell)), {On site desk 1}, NOT(CONTAINS("needed", @cell)))
-
@Lucas Rayala Thank you that is close it is also counting blanks. I tried adding NOT(ISBLANK but I just can't get the syntax right
=COUNTIFS({Summer Program}, "Yes",{On site desk 1}, NOT(CONTAINS("Virtual", @cell)), {On site desk 1}, NOT(CONTAINS("needed", @cell)),{On site desk 1}, NOT(ISBLANK, (@cell)))
-
Hi @latheobald89201, you need to exclude the extra coma in front of the "ISBLANK" term in your formula.
=COUNTIFS({Summer Program}, "Yes",{On site desk 1}, NOT(CONTAINS("Virtual", @cell)), {On site desk 1}, NOT(CONTAINS("needed", @cell)),{On site desk 1}, NOT(ISBLANK(@cell)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!