"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

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Ronald Anderson

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

Ronald Anderson

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.

Genevieve P.

Hi @Ronald Anderson

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

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.