Math & Trig
Count cells not equal to a value (without counting blank cells)
Pair "<>Value" with "<>" inside COUNTIFS so empty rows in pre-allocated ranges are not counted as active orders.
The hidden trap of COUNTIF(range, "<>Cancelled")
An empty cell is not equal to "Cancelled"—so COUNTIF(A2:A6, "<>Cancelled") counts every blank cell in your range right alongside active orders!
Adding a second criterion A2:A6, "<>" inside COUNTIFS (or subtracting COUNTIF from COUNTA) restricts the count strictly to non-empty cells that do not match the excluded status.
COUNTA minus COUNTIF is equivalent only when there are no formulas returning empty text: COUNTA counts those formulas. Prefer the explicit COUNTIFS nonempty criterion for that case. Spaces are still content.
| Row | OrderState (A) | COUNTIF "<>Cancelled" | COUNTIFS "<>Cancelled" & "<>" |
|---|---|---|---|
| 2 | Shipped | Counted | Counted |
| 3 | Cancelled | Excluded | Excluded |
| 4 | Processing | Counted | Counted |
| 5 | (empty) | Counted (trap!) | Excluded |
| 6 | Cancelled | Excluded | Excluded |
Blank-safe exclusion formula
Across A2:A6 (with A5 empty), COUNTIFS returns 2 active orders ("Shipped" and "Processing"), whereas single COUNTIF returns 3.
=COUNTIFS(A2:A6, "<>Cancelled", A2:A6, "<>")COUNTA minus COUNTIF check
COUNTA(A2:A6) - COUNTIF(A2:A6, "Cancelled") computes 4 non-empty cells minus 2 cancelled orders = 2.
=COUNTA(A2:A6) - COUNTIF(A2:A6, "Cancelled")Try the example
Use the original values or change them to test your understanding.