Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count cells not equal to a value in Excel and Google Sheets
Topic:COUNTIF & COUNTIFSExcel & Google Sheets
=COUNTIF(A2:A5,"<>"&"Pending")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Status |
| Pending |
| Approved |
| Rejected |
| Approved |
=COUNTIF(A2:A5,"<>"&"Pending")→3
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the cell where you want the count to appear.
- 2Type =COUNTIF( then select your data range (e.g., A2:A100).
- 3For the criteria, type "<>" concatenated with the value to exclude, such as "<>"&"Pending" or simply "<>Pending".
- 4Close the parenthesis and press Enter to get the count of cells that do not equal the specified value.
Tips
- To exclude blanks, use =COUNTIF(A2:A100,"<>""") — this counts all non-empty cells.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Is the comparison case-sensitive?
No, COUNTIF is case-insensitive, so 'pending', 'Pending', and 'PENDING' are all treated the same.
Can I exclude multiple values at once?
Yes — use =COUNTA(range)-COUNTIF(range,"Value1")-COUNTIF(range,"Value2") or sum multiple COUNTIF expressions.
More on COUNTIF & COUNTIFS
See all →Reference
COUNTIF
Count the cells in a range that meet a condition.
How-toCOUNTIF vs COUNTIFS: how they differ in Excel and Sheets
Learn the difference between COUNTIF (single condition) and COUNTIFS (multiple conditions) for counting in Excel and Google Sheets.
Error fixFix COUNTIF
COUNTIF not working in Excel and Google Sheets — fix criteria syntax, data-type mismatches, wildcard errors, and case-sensitivity misunderstandings.
ReferenceCOUNTIFS
Count cells that meet several conditions simultaneously.
How-toHow to use COUNTIF with a wildcard
Count cells that contain, start with, or end with specific text using COUNTIF with * and ? wildcard characters.
Error fixFix Wrong count
COUNTIF returns the wrong count in Excel and Google Sheets — fix wildcard over-matching, duplicate rows, hidden rows, blank cell handling, and date format issues.