COUNTIFS Formula: Not getting the desired result

Options

Hi there

My COUNTIFS formula is not calculating what I want, correctly.

We have x5 items that are past their delivery date. x3 Of the items are in the "Incomplete" stage, x1 item is in the "Ready for Collection" stage and x1 item is still in process.

I want to count the number of items in process past their delivery date, but it shouldn't include "Incomplete" and "Ready for Collection" items.

My formula is:

=COUNTIFS([Item status]:[Item status], OR(@cell <> "Ready for Collection", @cell <> "Incomplete"), [Estimated completion control]:[Estimated completion control], @cell = "Red")

"Red" indicates that the item is past its delivery date.

The formula calculates a 5. All x5 items. The correct answer is x1 item. It's a sheet summary formula.

What am I doing wrong?

Regards

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Your OR function captures everything as the Item status can only be 1 status, but you're asking it to check it is not 1 of 2 statuses. E.g. if a row is "Incomplete", it will not be "Ready for Collection" and so will get counted.

    The OR should be an AND:

    =COUNTIFS([Item Status]:[Item Status], AND(@cell <> "Incomplete", @cell <> "Ready for Collection"), [Estimated completion control]:[Estimated completion control], @cell = "Red")

    Sample output:

    Hope this helps, but if I've misunderstood something or you've any problems/questions then just post. 🙂

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Your OR function captures everything as the Item status can only be 1 status, but you're asking it to check it is not 1 of 2 statuses. E.g. if a row is "Incomplete", it will not be "Ready for Collection" and so will get counted.

    The OR should be an AND:

    =COUNTIFS([Item Status]:[Item Status], AND(@cell <> "Incomplete", @cell <> "Ready for Collection"), [Estimated completion control]:[Estimated completion control], @cell = "Red")

    Sample output:

    Hope this helps, but if I've misunderstood something or you've any problems/questions then just post. 🙂

  • Meyer Scholtz
    Options

    Hi Nick

    That solved the problem, thanks.

    Cheers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!