Statistical & Conditional Counting
Count Rows Not Equal to Multiple Criteria (COUNTIFS Multi-Exclusion)
Count active operational rows that simultaneously exclude Cancelled, Refunded, and Blank status entries—Avoiding the classic OR("<>A", "<>B") logical trap.
De Morgan's Law: why multi-exclusion requires AND (COUNTIFS), never OR
A frequent spreadsheet bug is summing COUNTIF(range, "<>Cancelled") + COUNTIF(range, "<>Refunded"), which counts every row at least once because "Cancelled" is not equal to "Refunded" and vice versa.
By De Morgan's law, NOT(Status = "Cancelled" OR Status = "Refunded") is equivalent to (Status <> "Cancelled") AND (Status <> "Refunded"). Pairing each "<>" condition inside COUNTIFS—plus "<>" to exclude blank rows—counts strictly active records.
| Requirement | Correct Logic | COUNTIFS Pair |
|---|---|---|
| Exclude Status 1 | AND Status <> D2 | B2:B8, "<>" & D2 |
| Exclude Status 2 | AND Status <> D3 | B2:B8, "<>" & D3 |
| Exclude Blank Cells | AND Status <> "" | B2:B8, "<>" |
Multi-Exclusion COUNTIFS Formula
Chain "<>" & ExcludedCell pairs on the same status range and append a final "<>" criterion so unpopulated rows are not counted as active.
=COUNTIFS(B2:B8, "<>" & D2, B2:B8, "<>" & D3, B2:B8, "<>")Try the example
Use the original values or change them to test your understanding.