COUNTIF Not Working? Common Causes and Fixes
By ExcelReady TeamUpdated 5 min read
The short answer
COUNTIF usually returns 0 or a low count because the criteria doesn't match the cells exactly. The most common causes are extra spaces in the data, numbers stored as text, missing quotes around text or operators, and a cell reference typed inside quotes. Clean the data with TRIM, write criteria like ">="&E1, and use wildcards for partial matches.
Key takeaways
- "Paid " with a trailing space is not "Paid" to COUNTIF; clean the column with TRIM or match with "Paid".
- Comparison criteria like ">=50" skip numbers stored as text, so convert them first.
- Text and operators go inside quotes; a cell reference goes outside, joined with &.
- COUNTIF only matches whole cells unless you add * or ? wildcards.
- COUNTIFS returns #VALUE! when its ranges are different sizes.
On this page+
- Why COUNTIF returns 0 or the wrong count
- Cause 1: extra spaces in the data
- Cause 2: numbers stored as text
- Cause 3: missing or misplaced quotes
- Cause 4: a cell reference inside the quotes
- Cause 5: you need a partial match
- Cause 6: dates in the criteria
- Cause 7: errors from COUNTIFS and long text
- Practise it
When COUNTIF is not working, the formula almost always runs fine. It just doesn't find what you think it should, so it returns 0 or a count that's too low. The cause is usually a tiny mismatch between the criteria and the cells: an extra space, a number stored as text, or missing quotes. This guide covers each cause with a quick test and a fix.
Why COUNTIF returns 0 or the wrong count#
COUNTIF counts cells that match the criteria exactly, apart from capital letters. It doesn't guess. If one character is different, the cell isn't counted, and you get no error to warn you.
The examples use this invoice list in A1:C7:
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Status | Amount |
| 2 | INV-01 | Paid | 120 |
| 3 | INV-02 | Paid␣ | 75 |
| 4 | INV-03 | Due | 200 |
| 5 | INV-04 | ␣Paid | 45 |
| 6 | INV-05 | Paid | 60 |
| 7 | INV-06 | Due | 310 |
The ␣ symbol marks a space you can't see in Excel: B3 ends with a space and B5 starts with one. Four invoices are paid, but =COUNTIF(B2:B7,"Paid") returns 2.
Cause 1: extra spaces in the data#
Extra spaces are the number one reason COUNTIF misses cells. They come from imports, copy and paste, and typing. "Paid " with a trailing space looks the same as "Paid" but doesn't match it.
To check a cell, use =LEN(B3). "Paid" has 4 characters, so a result of 5 means there's a hidden space.
There are two fixes:
- Clean the data. In a helper column, type
=TRIM(B2)and fill down. TRIM removes spaces at the start and end and turns double spaces into single ones. Then copy the helper column and paste it back as values. The TRIM function guide shows more examples. - Match around the spaces. Use wildcards so the spaces don't matter:
=COUNTIF(B2:B7,"*Paid*")This returns 4. Be careful: "*Paid*" would also count "Unpaid", so only use it when that can't happen. Cleaning the data is the safer long-term fix.
Cause 2: numbers stored as text#
If a column of numbers was imported as text, comparison criteria skip it. =COUNTIF(C2:C7,">=100") only counts real numbers. Text that looks like 120 is ignored.
Signs of text numbers: they sit on the left of the cell and often show a small green triangle. Select them, click the warning icon and choose Convert to Number. The guide to numbers stored as text covers other ways to convert them.
The same mismatch breaks lookups. If VLOOKUP is not working on the same data, text numbers are a likely cause there too.
Cause 3: missing or misplaced quotes#
Text and comparison operators must go inside double quotes. Numbers on their own don't need them.
| You want | Wrong | Right |
|---|---|---|
| Status is Paid | =COUNTIF(B2:B7,Paid) | =COUNTIF(B2:B7,"Paid") |
| Amount over 100 | =COUNTIF(C2:C7,>100) | =COUNTIF(C2:C7,">100") |
| Not equal to Due | =COUNTIF(B2:B7,"!=Due") | =COUNTIF(B2:B7,"<>Due") |
| At least 100 | =COUNTIF(C2:C7,"=>100") | =COUNTIF(C2:C7,">=100") |
Without quotes, Excel reads Paid as a name and returns #NAME?, and >100 without quotes won't even let you press Enter. Excel's "not equal to" is <>, and "greater than or equal to" is >=, never =>.
Cause 4: a cell reference inside the quotes#
When the criteria uses a value from another cell, the cell reference must stay outside the quotes. If E1 holds 100:
=COUNTIF(C2:C7,">="&E1)This returns 3 (120, 200 and 310). The & joins the operator text to the number in E1. If you write ">=E1", COUNTIF compares the amounts with the text "E1", finds nothing, and returns 0.
To match a cell exactly, use the cell on its own with no quotes at all: =COUNTIF(B2:B7,E2).
Cause 5: you need a partial match#
COUNTIF matches whole cells. "Paid" doesn't count "Paid in full" or "Part paid". To count cells that contain a word anywhere, use wildcards:
*means any number of characters:"*paid*"counts any cell containing "paid".?means exactly one character:"INV-0?"counts INV-01 to INV-09.
If the text really contains a * or ?, put a tilde in front: "~*". The article on COUNTIF with cells that contain text has more partial-match examples, and counting cells that are not blank explains "<>".
Cause 6: dates in the criteria#
Dates are numbers in Excel, so date criteria follow the same rules as numbers. Typing a date inside the criteria text, such as ">=1/3/2024", depends on your computer's date settings: one person's 1 March is another person's 3 January. Build the date with the DATE function instead, which always reads year, month, day. If column D held the invoice dates:
=COUNTIF(D2:D7,">="&DATE(2024,3,1))This counts dates on or after 1 March 2024 on any computer. If the count is still 0, check that the dates are real dates and not text. =ISNUMBER(D2) returns TRUE for a real date. Text dates sit on the left of the cell and need converting before COUNTIF can compare them.
Cause 7: errors from COUNTIFS and long text#
Some problems give an error instead of a wrong count:
- #VALUE! in COUNTIFS means the ranges are different sizes, such as
B2:B7withC2:C6. Every range must cover the same rows. See COUNTIF vs COUNTIFS for how the two differ. - #VALUE! with long criteria happens when the text you match is longer than 255 characters.
- #VALUE! with a closed file happens when COUNTIF points at another workbook that isn't open. Open the source file and recalculate.
If the count is still wrong, check that the range covers every row. A range that stops one row early drops the last record without any warning.
Practise it#
Practise COUNTIF in a live spreadsheet: you'll write text, number and cell-based criteria and see instantly whether each count is right. Then practise wildcards in COUNTIF and SUMIF criteria to handle partial matches. The COUNTIF function guide is a quick syntax reference, and the exercise on this page is a good first step.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Some statuses in B2:B7 have stray spaces, so =COUNTIF(B2:B7,"Paid") returns only 2. Click D2 and count every Paid invoice with the wildcard criteria *"Paid". Press Enter*.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Status | Paid count | |||
| 2 | Paid | ||||
| 3 | Paid | ||||
| 4 | Due | ||||
| 5 | Paid | ||||
| 6 | Paid | ||||
| 7 | Due | ||||
| 8 | |||||
| 9 | |||||
| 10 |
Tip: Put an asterisk on both sides of Paid, all inside the double quotes.
Practise this in a lesson
Frequently asked questions
Why is my COUNTIF returning 0?
The criteria doesn't match any cell exactly. Look for extra spaces in the data, numbers stored as text, missing quotes, or a range pointing at the wrong column.
Why is COUNTIF not counting text?
COUNTIF matches the whole cell, so "Paid" doesn't count "Paid in full" or "Paid " with a space. Use a wildcard like "Paid" for partial matches, or clean the cells with TRIM.
How do I use a cell reference in COUNTIF criteria?
Put the operator in quotes and join the cell with &, like =COUNTIF(C2:C9,">="&E1). If you write ">=E1" inside quotes, COUNTIF compares with the letters E1, not the cell value.
Is COUNTIF case-sensitive?
No. COUNTIF treats paid, Paid and PAID as the same. For a case-sensitive count, use SUMPRODUCT with EXACT, such as =SUMPRODUCT(--EXACT(B2:B9,"Paid")).