Excel COUNTIF Not Working? Fix Formula and Criteria Errors

If Excel COUNTIF returns the wrong result, first check that it is looking at the intended cells and that its criterion matches the data; then check for extra spaces, wildcard characters, numbers stored as text, or date and time differences. For items in A2:A10 and a criterion in D2, the basic formula is =COUNTIF(A2:A10,D2).

COUNTIF is simple, but a small difference between what a cell displays and what it contains can change the answer. The checks below help identify whether the problem is the formula, the criterion, or the underlying data.

How COUNTIF works

COUNTIF takes two arguments: a range to examine and a criterion that determines which cells count. It returns one number: the number of cells in that range that meet the criterion. The criterion can be a value, a cell reference, or a text expression containing a comparison operator.

For example, with product names in A2:A10 and the word Apple in D2, =COUNTIF(A2:A10,D2) counts cells that match the criterion. To count numeric values greater than 15 in B2:B10, use =COUNTIF(B2:B10,">15"). The comparison operator is part of the criterion, so it needs quotation marks when typed directly into the formula.

When the operator or threshold is in a cell, join them with the ampersand. With scores in B2:B10 and a threshold in D2, =COUNTIF(B2:B10,">"&D2) counts scores strictly greater than D2. If D2 contains 15 and the scores are 8, 12, 19, and 22, the result is 2. Greater than is not the same as greater than or equal to; use the appropriate operator for the question.

Check the range and the criterion

A formula can be syntactically valid and still count the wrong cells. Confirm that the first argument covers every intended row, and that it does not include a heading or omit recently added records. When copying a formula down, inspect whether the range shifted. If it should stay fixed, use absolute references.

With names in A2:A10 and a criterion in D2, a range locked for copying is =COUNTIF($A$2:$A$10,D2). The dollar signs keep the data range fixed while D2 can change to D3, D4, and so on. In Excel, select a reference in the formula bar and press F4 to cycle through reference styles on Windows. On a Mac, the equivalent shortcut can depend on keyboard settings; use the Fn key with F4 if needed.

Also verify that the criterion cell contains what you think it contains. A trailing space in D2, for example, can make an exact text criterion differ from the visible word. Click the cell and inspect the formula bar, or compare its length with the apparent text. If the formula uses a typed criterion rather than a cell reference, check its quotation marks and punctuation.

COUNTIF accepts one criterion. If you need to count rows that satisfy more than one condition, use COUNTIFS instead. For example, with department names in A2:A20 and results in B2:B20, a COUNTIFS formula can require both a department and a result to match. Keep each criteria range aligned to the same rows so each condition is tested against the corresponding record.

Understand exact matches, wildcards, and case

For text, COUNTIF is not case-sensitive: criteria for Apple and apple treat those spellings as the same match. It can also interpret the asterisk and question mark as wildcards. An asterisk matches any sequence of characters, including no characters; a question mark matches one character. This is useful for deliberate partial matches, but can cause surprising counts if the criterion contains either symbol.

=COUNTIF(A2:A10,"App*") counts entries that begin with App, such as Apple or Application.
=COUNTIF(A2:A10,"?at") counts three-character entries ending in at, such as cat or bat.
=COUNTIF(A2:A10,"~*") counts cells containing a literal asterisk as the entire criterion, rather than treating the asterisk as a wildcard.

In those examples the range is A2:A10. The tilde escapes a wildcard character: use ~* to look for an asterisk and ~? to look for a question mark. If a count is unexpectedly high, inspect the criterion for wildcard characters and decide whether you wanted an exact value or a partial match. COUNTIF is not a suitable way to distinguish text that differs only by capitalization; that requires a different, case-sensitive approach.

Look for hidden spaces and imported text

Copied data often contains leading or trailing spaces, nonbreaking spaces, line breaks, or other characters that are difficult to see. A cell displaying Northwind may contain a space after the word, so an exact criterion for Northwind will not necessarily match it. The same issue can affect a criterion copied from a website or another system.

For ordinary extra spaces, use a helper column to inspect or clean the source rather than changing the original data without checking. With imported text in A2, put =TRIM(A2) in B2 and fill it down; compare the cleaned values with the original. TRIM removes extra standard spaces but does not remove every kind of invisible character, including a nonbreaking space. If that is the problem, with the source text in A2 use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) in a helper cell, then fill down and review the results.

These cleanup formulas assume the original value is in A2. They produce cleaned text in another cell; COUNTIF must then use the cleaned helper range if that is what you want to count. For example, after filling the helper formula through B2:B10, a count of cleaned values matching D2 can use =COUNTIF(B2:B10,D2). Keep a copy of the source data before bulk cleanup, especially when the imported characters may carry meaning.

Check numbers stored as text

A value that looks like 100 may be the number 100 or the text string “100”. Formatting can make the two look identical, while sorting, arithmetic, and criteria behavior can reveal a difference. This commonly happens after importing a CSV, pasting data from a web page, or entering a leading apostrophe. A number format changes how a value is displayed; it does not necessarily convert text into a numeric value.

With a suspected value in A2, test its type in a spare cell using =ISNUMBER(A2); use =ISTEXT(A2) to test whether it is text. A result of TRUE from ISNUMBER confirms a numeric value, while TRUE from ISTEXT indicates text. These checks do not fix the data, but they help explain a COUNTIF mismatch. Inspect a few cells from the range, including ones that counted and ones that did not.

If a column is meant to contain numbers, convert it only after confirming that interpretation is appropriate. Depending on the data, Excel’s Data > Text to Columns workflow can convert numeric text; you can also use a helper formula such as =VALUE(A2) when A2 contains text Excel can interpret as a number. Review the converted results before replacing the source. Avoid assuming that every digit-only value should be numeric: product codes and postal codes are often identifiers, not quantities.

Count dates and values that include times

Excel stores dates as serial numbers, and a cell may contain both a date and a time even when its number format displays only the date. As a result, an exact date criterion can miss entries that have a time component. For example, a displayed date at midnight and the same displayed date at 2:30 p.m. are different underlying values.

To count dates on or after January 1, 2026, with dates in A2:A10, use =COUNTIF(A2:A10,">="&DATE(2026,1,1)). DATE creates a real Excel date value, avoiding ambiguity from regional date text such as 1/2/2026. If the sample range contains January 1, January 5, and December 31, 2025, the result is 2.

To count every date and time in January 2026, with timestamps in A2:A10, use =COUNTIFS(A2:A10,">="&DATE(2026,1,1),A2:A10,"<"&DATE(2026,2,1)). This includes January 31 entries with times because the upper limit is the start of February, not a single timestamp on January 31. The same range appears twice because COUNTIFS applies a lower-bound and an upper-bound condition to it.

Investigate blanks, calculation, and formula errors

A criterion of an empty string is often used to count blank-looking cells. With values in A2:A10, =COUNTIF(A2:A10,"") counts cells that Excel treats as empty for this criterion, including cells whose formulas return an empty string. That does not necessarily mean the cells contain no formula. Conversely, a cell containing a space is not the same as an empty string. Check the formula bar or use a helper check if the distinction matters.

If a result does not update after changing source data, check calculation mode. In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and choose Automatic if that is appropriate for the workbook. Formulas > Calculate Now recalculates open worksheets; F9 also recalculates workbooks in automatic calculation mode. On Mac, the Formulas tab includes calculation controls, though the precise layout and keyboard shortcut can differ by version and keyboard settings. Calculation mode can affect other formulas too, so consider the workbook’s intended settings before changing it.

When Excel displays an error instead of a count, examine the formula syntax and the cells in the referenced range. Common causes include a missing closing parenthesis, a misspelled function name, an invalid reference, or a source cell that already contains an error. In many language and regional settings Excel uses commas between function arguments; some installations expect semicolons instead. If a formula pasted with commas is rejected, check the list separator used by your Excel settings rather than replacing commas at random.

A practical troubleshooting sequence

Work from the simplest checks to the less visible data issues. This avoids changing clean source data when the real cause is only a range reference or an unintended wildcard.

Click the COUNTIF cell and verify the function, both arguments, parentheses, and referenced cells.
Select the range in the formula and confirm it includes the rows you intend to count, without an accidental heading or missing records.
Check the criterion in the formula or referenced cell, including comparison operators, spaces, and wildcard characters.
Test representative source cells for text-versus-number differences, hidden spaces, and date-time values.
Check whether calculation is automatic and whether the workbook shows a formula error or simply an unexpected number.
Try the formula on a small, known sample with a manually verifiable answer before changing the full dataset.

For a quick comparison, filter the data to show the values you believe should match and count the visible records. Filtering is a diagnostic aid, not proof that the formula and filter use identical matching rules. If the filtered values look alike but the count differs, inspect their underlying values and the exact COUNTIF criterion.

Frequently Asked Questions

Why does COUNTIF return 0 when the text looks identical?
Look for leading or trailing spaces, nonbreaking spaces, punctuation differences, and a criterion that refers to a different cell than expected. Also check whether the range contains formulas or imported text that displays similarly but has different underlying characters.

Can COUNTIF count cells that contain part of a word?
Yes. Use wildcards in the criterion: an asterisk represents any number of characters, and a question mark represents one character. For an exact literal asterisk or question mark, precede it with a tilde.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF text matching is case-insensitive, so capitalization alone will not make two text entries count differently. Use a case-sensitive method if capitalization is part of the requirement.

Why does COUNTIF miss a date that is displayed in the range?
The cell may contain a time as well as the displayed date, or the date may be stored as text. Compare the underlying values and, for periods, use a lower date boundary and an exclusive upper boundary rather than relying on a displayed format.

Can COUNTIF apply two conditions at once?
No. COUNTIF accepts one criterion. Use COUNTIFS when a count must satisfy multiple conditions, such as a matching category and a date range; make sure each criteria range covers corresponding rows.

Why does COUNTIF not recalculate after I edit the data?
The workbook may be set to manual calculation. Check Formulas > Calculation Options in Excel for Windows and recalculate if needed. If the result remains wrong, verify that the edited cell is inside the formula’s 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.