"Not Equal To" Help
I have the below formula that works with the exception of the OR function. It is not omitting blank cells or cells that contain "Vacant" in the Emp No in its count.
=COUNTIFS({Route}, $[Primary Column]@row, {Landside Storm helper}, 0, {Operations Report Range 1}, <>1, {Day/Night}, [Storm Type Contact]@row, {Emp No}, OR(@cell <> "", @cell <> "Vacant"))
Thank you for your assistance,
Ron
Answers
-
OR can be tricky in a COUNTIFS with blank. For example, a cell that is not blank will be a cell that has "Vacant", and a cell that has "Vacant" is a cell that is not blank, so we can't use OR... they cancel each other out.
Try using AND instead - does this work?
=COUNTIFS({Route}, $[Primary Column]@row, {Landside Storm helper}, 0, {Operations Report Range 1}, <>1, {Day/Night}, [Storm Type Contact]@row, {Emp No}, AND(@cell <> "", @cell <> "Vacant"))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you for your response. Using the AND function yields no results.
This one has me a bit baffled. I don't understand why the OR function doesn't work either.
-
Let's try a different approach. First we can COUNT all the non-blank values, then we can COUNT all the values that have "Vacant" and subtract that number from the original count.
=COUNTIFS({Route}, $[Primary Column]@row, {Landside Storm helper}, 0, {Operations Report Range 1}, <>1, {Day/Night}, [Storm Type Contact]@row, {Emp No}, <> "") - COUNTIFS({Route}, $[Primary Column]@row, {Landside Storm helper}, 0, {Operations Report Range 1}, <>1, {Day/Night}, [Storm Type Contact]@row, {Emp No}, "Vacant")
Does this give you the correct result?
If not, it would be helpful to see screen captures of your source sheet, but please block out sensitive data. It may not be finding the values you're looking for in each of your references. We could try breaking it out to make sure there's a COUNT for each of your Columns & Criteria.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!