COUNTIF Not Counting Correctly in Excel

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

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

For example, count rows where the status is 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:

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:

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

COUNTIF text criteria are case-insensitive, so Paid, PAID, and paid match the same criterion. If capitalization must be significant, COUNTIF is not the right test by itself.

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:

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

For dates, use a real date value:

Use wildcards intentionally

If you want exact matches, use exact criteria:

If you want partial matches, use wildcards:

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.

Use ~* to match a literal asterisk and ~? to match a literal question mark.

Know the COUNTIF limits

Microsoft documents incorrect COUNTIF results when a direct text criterion is longer than 255 characters. Join shorter text pieces or use another approach when matching very long strings. COUNTIF also does not count based on font or fill color; store the status in a cell value if it must drive a reliable count.

Common COUNTIF fixes

Do this with AI

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.

Related guides: convert text to numbers and use COUNTIFS in Excel.

Sources

• Microsoft Support: Use the COUNTIF function and troubleshoot common errors
• Microsoft Support: Correct a #VALUE! error in COUNTIF/COUNTIFS