COUNTIFS Formula: Not getting the desired result

Meyer Scholtz

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

Nick Korna

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. 🙂

Nick Korna

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

Hi Nick

That solved the problem, thanks.

Cheers