Why Is Excel COUNTIF Not Working? Common Fixes

Excel COUNTIF usually fails because its criteria do not match the cells as stored, the range points to the wrong data, or the formula is not recalculating. Check the selected range and the exact criterion first, then look for extra spaces, text-versus-number differences, wildcard rules, and calculation settings.

How COUNTIF works

COUNTIF counts cells in one range that meet one condition. Its two arguments are the cells to inspect and the criterion to apply. The criterion can be a number, text, comparison, wildcard pattern, or a reference to a cell containing the condition. For a basic example, with values in A2:A20 and the word Paid in A2:A20, the formula is:

=COUNTIF(A2:A20,"Paid")

The result is the number of cells in A2:A20 that match Paid. Text matching is not case-sensitive, so Paid and PAID count as the same criterion. COUNTIF does not count a cell merely because it looks similar: a trailing space, a different character, or a value stored in another form can prevent a match.

COUNTIF has only one condition. If the task requires two or more conditions, use COUNTIFS, which applies criteria to corresponding ranges. This distinction matters because trying to put multiple conditions into one COUNTIF criterion will not produce a multi-condition count.

Check the range and references first

A correct criterion cannot fix a range that misses the data. Click the formula cell and inspect the colored range outline in the worksheet, or select the range portion in the formula bar to see which cells it refers to. Confirm that the range includes all relevant rows and does not accidentally include a heading while excluding the last data row.

Relative references can also move when you copy a formula. With product names in A2:A100 and a requested product in D2, this formula counts the item named in D2:

=COUNTIF(A2:A100,D2)

If you copy that formula down, A2:A100 may shift to A3:A101. To keep the data range fixed while copying, make it absolute. With the same layout, use:

=COUNTIF($A$2:$A$100,D2)

The dollar signs lock the range, while D2 remains relative and can change to D3, D4, and so on. In Excel for Microsoft 365 on Windows, F4 while editing a selected reference cycles through reference types. On a Mac, the equivalent commonly depends on the keyboard settings; use the Fn key with F4 where necessary, or type the dollar signs directly.

Also check for a mistaken sheet or workbook reference, especially if the formula was copied from another worksheet. A range can look plausible in the formula but point to an old sheet, a similarly named column, or an incomplete imported-data area.

Use criteria syntax that matches the task

Comparison operators must be included inside quotation marks when typed as criteria. With scores in B2:B100, this counts values greater than 80:

=COUNTIF(B2:B100,">80")

To compare against a value in a cell, join the operator in quotes to the cell reference. With scores in B2:B100 and the threshold in D1, use:

=COUNTIF(B2:B100,">"&D1)

Here, & combines the greater-than operator with D1’s value. A frequent mistake is to write the operator outside quotation marks or to combine it with a cell reference incorrectly. When the formula returns zero unexpectedly, confirm that the criterion is a comparison and not literal text that happens to contain a greater-than sign.

To count a specific text value, put it in quotation marks in the formula, or refer to a cell that contains the text. Check spelling and punctuation, including apostrophes and hyphens. COUNTIF is case-insensitive, so changing capitalization is not a fix for a mismatch, but changing a dash, space, or other character may be.

Look for spaces and wildcard characters

Imported or manually entered text can contain leading or trailing spaces. A cell that appears to contain North may actually contain North followed by a space, and COUNTIF treats those as different text. To inspect a suspicious cell, click it and look in the formula bar; widening the column or using the keyboard to move through the text can also reveal a difference. Cleaning a copy of the data with TRIM may help, but TRIM does not remove every kind of invisible character, such as a nonbreaking space copied from a web page.

COUNTIF supports two wildcard characters in text criteria: an asterisk matches any number of characters, and a question mark matches exactly one character. With descriptions in A2:A100, this counts cells containing the word east anywhere in the text:

=COUNTIF(A2:A100,"*east*")

That pattern also matches longer words containing those letters, not only the standalone word east. To match a literal asterisk or question mark, put a tilde before the character. For example, with labels in A2:A100, this counts cells containing a literal asterisk:

=COUNTIF(A2:A100,"*~**")

The first and last asterisks allow any surrounding text; ~* represents the literal asterisk. Wildcards apply to text matching, so a pattern that is too broad can return a count that seems unexpectedly high.

Check numbers, dates, and imported data

Values that look alike on screen may be stored differently. A number entered as numeric 25 is not necessarily equivalent in every context to the text string 25, and imported files often mix the two. Check alignment and cell formatting for clues, but remember that formatting alone does not convert a value: changing a cell from Text to General does not always turn existing text into a number. Use a consistent data type, or convert a cleaned copy of the column before counting.

Dates are stored by Excel as serial numbers and displayed according to the cell’s number format. A criterion typed as text may not mean the same thing as an actual date value. For example, with real Excel dates in C2:C100, this counts dates on or after January 1, 2026:

=COUNTIF(C2:C100,">="&DATE(2026,1,1))

The DATE function creates a date value rather than relying on a typed date string whose interpretation can vary with regional settings. If the dates were imported as text, convert them to actual dates first; changing their display format alone is not enough. For a date period with both a start and end boundary, COUNTIFS is generally the clearer choice.

Diagnose zero, an error, or a stale result

A result of zero often means the formula is valid but no cells match. An error usually points to a problem with a reference, an invalid argument, or an error value involved in the formula. A result that does not change after editing the source data may indicate that calculation is set to manual. These symptoms call for different checks, so do not assume every wrong count is a criteria problem.

Zero: Verify that the range contains the intended records, then compare the criterion with a cell’s actual contents. Check spaces, data type, and wildcard placement.
An error: Check for a misspelled function name, unbalanced parentheses, a deleted or invalid reference, and the exact text in the criteria argument.
A count that is too high: Look for a wildcard that matches more than intended, duplicate records, or a range that includes extra rows.
A result that stays unchanged: Check whether calculation is automatic and whether the formula refers to the cells being edited.

In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and choose Automatic if the workbook should recalculate as inputs change. Excel for Mac has a corresponding Calculation Options control on the Formulas tab; the precise ribbon layout can vary by release. After correcting the setting, recalculate and see whether the result updates. Manual calculation can be intentional in a large workbook, so confirm before changing it.

Inspect the formula bar for a leading apostrophe or an initial equals sign stored as text. If Excel displays the formula itself in the cell instead of a result, the cell may be formatted as Text, or Show Formulas may be enabled. Change the cell format to General, re-enter the formula, and check Formulas > Show Formulas. These display issues are different from COUNTIF returning an incorrect numeric result.

When COUNTIFS or another method is better

Use COUNTIFS when every counted row must meet multiple conditions. For example, with regions in A2:A100 and statuses in B2:B100, this counts rows where the region is West and the status is Open:

=COUNTIFS(A2:A100,"West",B2:B100,"Open")

Both ranges should cover corresponding rows and have matching dimensions. If the task is to count cells meeting either of two alternatives, adding two COUNTIF results can work when the categories cannot overlap. If a cell could meet both criteria, adding the results may count that cell twice; define the intended logic before choosing a formula.

COUNTIF is not case-sensitive. If the requirement is to distinguish upper- and lowercase text, COUNTIF alone is not suitable; an exact, case-sensitive approach needs a different formula design. Similarly, counting based on a condition spread across several columns may require COUNTIFS or a helper column. A helper column can make complicated rules easier to audit because each row’s eligibility is visible before the final count is calculated.

A reliable troubleshooting sequence

Rather than rewriting the formula repeatedly, isolate the cause in a consistent order. Start with a small, known example and compare the formula’s range and condition against the actual cells. If the result remains unclear, test a simpler condition, then add complexity back one part at a time.

Click the formula cell and verify that it begins with an equals sign and uses the intended range.
Select the range reference in the formula bar and confirm the highlighted cells include the right records.
Test one known matching value as the criterion, using a cell reference if practical.
Inspect a supposed match for spaces, hidden characters, text-versus-number differences, or a date stored as text.
Review operators and wildcard characters, then check whether a broad pattern explains an excessive count.
Confirm calculation is automatic if the displayed result is stale, and recalculate the workbook if appropriate.
Switch to COUNTIFS or a helper column if the real requirement involves multiple conditions.

For repeatable reports, consider converting the data range to an Excel table using Insert > Table. Tables expand as new rows are added, which can reduce the chance that a fixed range omits recent records. Still verify the formula after creating or copying it: a table does not repair inconsistent data or an incorrectly chosen criterion.

Frequently Asked Questions

Why does COUNTIF return zero when the value is visibly there?
Most often the stored value differs from the criterion because of an extra space, an invisible character, a number stored as text, or a date stored as text. Confirm that the cell is inside the range and compare its actual contents with the criterion.

Does COUNTIF distinguish uppercase and lowercase letters?
No. COUNTIF text matching is not case-sensitive, so it treats entries such as West and WEST as matches for the same text criterion. Use a different formula approach if capitalization must distinguish records.

Can COUNTIF count cells that contain a word rather than equal it?
Yes. Use an asterisk before and after the word in the criterion to match text with characters surrounding it. Remember that this also matches the letters inside longer words, so inspect the pattern’s scope.

Why does my COUNTIF formula show in the cell instead of a number?
The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be turned on. Set the cell to General and re-enter the formula, and check Formulas > Show Formulas.

Why does COUNTIF not update when I change a value?
Check Formulas > Calculation Options and select Automatic if the workbook is intended to recalculate immediately. Also confirm that the changed cell is within the formula’s referenced range.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.