Excel

COUNTIF Not Counting Correctly in Excel

COUNTIF can miss rows when criteria, spaces, numbers, dates, or ranges do not match. Learn the common causes and clean fixes.

June 12, 2026/5 min read

COUNTIF should be simple: count the cells in a range that match one condition. When it returns the wrong number, the issue is usually not the function itself. It is usually a mismatch between the criteria and the data.

The fastest fix is to check the range, the criteria type, and the source values before rewriting the whole formula.

The syntax

=COUNTIF(range, criteria)

range - the cells you want to count
criteria - the condition a cell must meet

For example, count rows where the status is Paid:

=COUNTIF(C2:C200,"Paid")

Check that the range matches the data

The most basic mistake is counting the wrong column or using a range that stops too early. If your expense tracker grows to row 300 but the formula still uses C2:C200, the newest rows will not be included.

Use a range that covers the real table:

=COUNTIF(C2:C300,"Paid")

If the table keeps growing, consider converting the data to an Excel Table so the range expands more reliably.

Watch for extra spaces

COUNTIF treats Paid and Paid as different text values. This happens often after exports, copy-paste work, or manual cleanup.

If the source data has trailing spaces, clean the column with TRIM before relying on counts:

=TRIM(C2)

Then count the cleaned helper column instead of the raw imported values.

TIP

If one value looks correct but does not count, click into the cell and check for leading or trailing spaces before changing the formula.

Make numbers and dates real values

COUNTIF can return confusing results when numbers or dates are stored as text. A transaction amount that looks like 1000 may actually be text from a bank export. A due date may be a typed label instead of a real date value.

For numeric criteria, do not put the number in quotes unless you are using an operator:

=COUNTIF(D2:D200,1000)

For comparisons, put the operator and number together in quotes:

=COUNTIF(D2:D200,">1000")

For dates, use a real date value:

=COUNTIF(E2:E200,DATE(2026,6,12))

Use wildcards intentionally

If you want exact matches, use exact criteria:

=COUNTIF(B2:B200,"Marketing")

If you want partial matches, use wildcards:

=COUNTIF(B2:B200,"*Marketing*")

This matters in sheets where category names drift, such as Marketing, Marketing - Ads, and Marketing - Events. A wildcard may be right for a broad review, but it can also overcount if unrelated labels contain the same word.

Common COUNTIF fixes

Problem	Why it happens	Fix
New rows are missing	Formula range stops too early	Expand the range or use a table
Visible values are not counted	Extra spaces or hidden characters	Clean with `TRIM` or normalize labels
Dates do not match	Dates are stored as text	Convert to real dates and use `DATE()`
Numbers do not match	Numbers are stored as text	Convert the source values to numbers
Too many rows are counted	Wildcard is too broad	Use exact criteria or a tighter wildcard

The Griddy way

COUNTIF problems are tedious because the formula can look correct while the source data is messy.

"Find why these COUNTIF totals do not match the source table, clean the category labels, and update the formulas without changing the layout"

Griddy can inspect the formula ranges, normalize source values, and repair the counts so the summary matches the actual sheet.

Skip the manual work

Describe it. Griddy does it.

Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.

Excel Plugin Sheets Plugin

Use this on real templates

Fix counts before weekly review starts

COUNTIF mistakes usually come from messy labels, short ranges, text numbers, or date values that do not match the criteria reviewers expect.

Finance

All posts