"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

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!