Why COUNTIF Counts Wrong: 6 Causes and How to Fix Each

Q: How do I count cells that are not blank?

Use `=COUNTIF(range,"<>")` or `=COUNTA(range)`.

Q: Is COUNTIF case-sensitive?

No. To count only an exact match including case, use `=SUMPRODUCT(--EXACT(range,"Text"))`.

Last updated: 5 October 2026 · Download: countif-counts-wrong-test-workbook.xlsx, with one sheet per cause, a broken formula and a fixed formula on each.

COUNTIF looks like the simplest function in Excel: count the cells that match something. When the count comes out too low, too high, or 0, Excel doesn't show an error. It counts exactly what you asked for, which isn't always what you meant.

This guide covers six reasons COUNTIF and COUNTIFS return the wrong number, with a test for each and the fixed formula. Every example is in the downloadable workbook: the data is in column H, the formulas in column B, and column C shows the count you should get.

First: filter and count by hand

Before you change the formula, apply a filter to the column (Data › Filter) and choose the value you're counting. The status bar at the bottom of Excel shows how many rows are visible.

The filter finds more rows than COUNTIF: some cells look like a match but aren't. See causes 1, 2 and 4.
COUNTIF finds more than the filter: the criteria match things you didn't intend. See cause 5.
COUNTIF returns 0 but the filter finds rows: the criteria themselves are written wrongly. See causes 3 and 6.

Cause 1: some numbers are stored as text

Sheet "1 Text numbers" in the workbook.

Column H has eight scores. Three of them, 88, 64 and 12, are stored as text, which is common after pasting from a web page or importing a CSV file. A comparison criterion such as ">50" only counts real numbers, so the text values are skipped:

=COUNTIF(H6:H13,">50")   → 3   (should be 5: 72, 88, 91, 64, 55)

Test: =COUNT(H6:H13) counts only real numbers. It returns 5 here, not 8, so three of the values are text. Text numbers also align to the left of the cell by default and often show a small green triangle.

Fix: convert the column to real numbers. Select it and use Data › Text to Columns › Finish. To count correctly without changing the data:

=SUMPRODUCT(--(VALUE(H6:H13)>50))   → 5

VALUE turns each cell into a number, the comparison gives TRUE or FALSE, -- turns those into 1 and 0, and SUMPRODUCT adds them up. VALUE returns an error on cells that can't be read as numbers, such as blanks or words, so use this only on a column that should contain only numbers.

Cause 2: extra spaces in the data

Sheet "2 Hidden spaces".

COUNTIF compares the whole cell. Two of the five "Paid" entries in the example aren't exactly "Paid": one is Paid with a trailing space, and one is Paid with a leading space. Both look identical on screen:

=COUNTIF(H6:H13,"Paid")   → 3   (should be 5)

Test: =LEN(H7) returns 5, although "Paid" has 4 letters.

Fix: clean the column. Add a helper column with =TRIM(H6), fill it down, then copy it and use Paste Special › Values over the original. To count without editing the data:

=SUMPRODUCT(--(TRIM(H6:H13)="Paid"))   → 5

A wildcard such as "*Paid*" would also find them, but it matches "Unpaid" too, which is a good example of why wildcards need care (see cause 5).

If TRIM doesn't fix it, the data may contain non-breaking spaces from a web page, which TRIM doesn't remove. The VLOOKUP #N/A guide shows how to find and remove them.

Cause 3: the cell reference is inside the quotes

Sheet "3 Quoted ref".

The goal is to count orders of at least the threshold in J6, which is 500. This version is the most common mistake with COUNTIF:

=COUNTIF(H6:H13,">=J6")   → 0

Everything inside the quotation marks is plain text, so COUNTIF compares each order with the letters "J6". Nothing matches, and the count is 0. The operator stays inside the quotes, and the cell is joined on with &:

=COUNTIF(H6:H13,">="&J6)   → 5

The same rule applies to dates. Use ">="&DATE(2026,2,1) or ">="&K2, never ">=K2", and avoid typed dates such as ">=1/2/2026", because Excel reads them according to your regional settings.

Test: look at the criterion. If a cell address appears inside the quotation marks, this is the cause.

Cause 4: the dates include times

Sheet "4 Dates + times".

System exports, form responses and logs often store a date and time together, even when the cell is formatted to show only the date. Excel stores a time as a fraction of a day, so 1 February 2026 at 09:15 is a slightly larger number than 1 February 2026 at midnight.

COUNTIF with a date tests for equality, so only the entries at exactly midnight match:

=COUNTIF(H6:H13,DATE(2026,2,1))   → 1   (four entries fall on 1 February)

Test: select a date cell and change its format to General. A whole number such as 46054 is a date alone. A decimal such as 46054.385 includes a time.

Fix: count a range from the start of the day up to, but not including, the start of the next day:

=COUNTIFS(H6:H13,">="&DATE(2026,2,1),H6:H13,"<"&DATE(2026,2,2))   → 4

The same pattern counts a whole month: from the first of the month up to, but not including, the first of the next month.

Cause 5: the criteria contain * or ?

Sheet "5 Wildcards".

In COUNTIF and COUNTIFS, * stands for any number of characters and ? for any single character. That's useful for "starts with" counts, but a problem when the characters are part of the real value. The part code A*-10 appears twice in the list:

=COUNTIF(H6:H13,"A*-10")   → 5   (also counts AB-10, AC-10 and AE-10)

COUNTIF reads A*-10 as "A, then anything, then -10".

Fix: put a tilde ~ in front of the * or ? to match it literally:

=COUNTIF(H6:H13,"A~*-10")   → 2

Watch for unintended wildcards in the other direction too. "*Paid*" counts "Unpaid", and "West*" counts "Westfield". When you need an exact match, use the plain value without wildcards.

Cause 6: two criteria on the same column

Sheet "6 OR criteria".

COUNTIFS counts rows where every criteria pair is true at the same time. That's AND logic. This formula is meant to count rows that are West *or* East:

=COUNTIFS(H6:H13,"West",H6:H13,"East")   → 0

No cell can be both West and East, so the answer is always 0.

Fix: count each value separately and add the results:

=COUNTIF(H6:H13,"West")+COUNTIF(H6:H13,"East")   → 5

For a longer list, pass the values as an array constant:

=SUMPRODUCT(COUNTIF(H6:H13,{"West","East"}))   → 5

Two criteria on the same column are correct when both can be true at once, as in the date range in cause 4.

A related point: COUNTIF ignores case

COUNTIF treats yes, Yes and YES as the same value. That's usually what you want. If upper and lower case must count as different, use EXACT, which is case-sensitive:

=SUMPRODUCT(--EXACT(H6:H13,"Yes"))

Checklist

When COUNTIF gives the wrong count:

Filter the column and compare the visible row count with the formula.
Counting numbers with > or <? Check COUNT for numbers stored as text (cause 1).
Does LEN show extra characters in the cells you expected to match? (cause 2)
Is a cell address inside the quotes? Use ">="&J6 (cause 3).
Counting dates? Check for hidden times, and count from the start of one day up to the start of the next (cause 4).
Do the criteria contain * or ?? Escape them with ~ (cause 5).
Two criteria on one column in COUNTIFS meant as OR? Add two COUNTIFs instead (cause 6).

COUNTIF and SUMIFS share most of these traps. If a SUMIFS total looks wrong, see Why SUMIFS returns 0. To highlight the matching rows instead of counting them, the Conditional Formatting Builder writes the rule formula for you.

FAQ

Why does COUNTIF return 0 when I can see matching cells? Usually the criteria don't match exactly. There may be hidden spaces in the data, a cell reference typed inside the quotes, or a date that includes a time. Filter the column to see which cells should match, then compare one of them with LEN and ISNUMBER.

What's the difference between COUNTIF and COUNTIFS? COUNTIF takes one range and one condition. COUNTIFS takes one or more range and condition pairs, and counts only rows where all of them are true.

How do I count cells that are not blank? Use =COUNTIF(range,"<>") or =COUNTA(range).

Is COUNTIF case-sensitive? No. To count only an exact match including case, use =SUMPRODUCT(--EXACT(range,"Text")).

Does the test workbook work in Google Sheets? Yes. Upload it to Google Drive and open it with Google Sheets. It uses COUNTIF, COUNTIFS, COUNT, SUMPRODUCT, VALUE, TRIM, LEN and DATE, which Google Sheets supports.