"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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!