How to count cells not equal to a value
✓ Verified in LibreOffice 25.8.7.3Count every cell except those matching a specific value.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =COUNTIF(A2:A6,"<>apple") | The not-equal operator is <> inside the quoted criterion. |
| Google Sheets | =COUNTIF(A2:A6,"<>apple") | Identical. |
| LibreOffice Calc | =COUNTIF(A2:A6,"<>apple") | Identical. |
How it works
COUNTIF's criterion accepts <> for "not equal": three cells (banana, cherry, banana) aren't "apple". Caution: <> also counts BLANK cells in the range as not-equal — add -COUNTBLANK(A2:A6) if you only want non-blank mismatches. To reference a cell, concatenate: =COUNTIF(A2:A6,"<>"&C1).
Verified, not just documented
We ran =COUNTIF(A2:A6,"<>apple") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 3 — exactly the expected result. Every formula here is confirmed by actually executing it.