Excel COUNTIF Not Counting Properly? Fix the Cause

If Excel COUNTIF is not counting properly, check the criterion, the cells’ actual contents, and the range first: most mismatches come from extra spaces, numbers stored as text, or a criterion that does not match what is in the cells. With the items to check in A2:A6, use =COUNTIF(A2:A6,"Apple") to count cells matching Apple; COUNTIF is not case-sensitive.

How COUNTIF works

COUNTIF checks one range against one criterion and returns the number of cells that meet it. Its syntax is COUNTIF(range, criteria): the range is where Excel looks, and the criterion describes what to count. The criterion can be text, a number, a comparison such as greater than 10, a wildcard pattern, or a reference to another cell.

For example, suppose A2:A6 contains Apple, pear, Apple, a blank cell, and apple. With those values in A2:A6, =COUNTIF(A2:A6,"Apple") returns 3. COUNTIF treats Apple and apple as the same text for counting. It does not, however, treat every visually similar value as identical: a trailing space, a nonbreaking space, or a different character can make a cell fail an exact-text criterion.

COUNTIF also counts cells in hidden or filtered-out rows. It is not a “count only what I can currently see” function. That distinction matters when a filtered table appears to show fewer matching entries than the formula reports.

Check the criterion and its syntax

A common issue is putting a comparison operator outside the quoted criterion or leaving out the quotation marks around text. For text, use a criterion such as "Apple". For a comparison, combine the operator and value inside the criterion string. With numeric values in B2:B100, the formula =COUNTIF(B2:B100,">100") counts values greater than 100; a value equal to 100 is not included.

If the comparison value is in a cell, join the operator to that reference. With numbers in B2:B100 and the threshold in D1, use =COUNTIF(B2:B100,">"&D1). If D1 contains 100, this counts values above 100. Writing ">D1" instead would ask Excel to compare against the literal text D1, not the value in that cell.

When the criterion itself is a cell reference, do not put the reference in quotation marks. With items in A2:A100 and the value to find in D2, use =COUNTIF(A2:A100,D2). Check that D2 contains the same kind of value as the entries in the range, and that it does not include an unintended space.

Find extra spaces and hidden characters

Cells can look identical while containing different characters. A copied value may have a leading or trailing ordinary space, or a nonbreaking space imported from a website. COUNTIF does not automatically remove these characters. To inspect a suspicious cell, select it and look in the formula bar; you can also compare its length with the length of a clean example. A different character count is a useful clue, although it does not identify the character by itself.

For a small list, edit or re-enter the affected values. For a larger list, a helper column is often safer because it preserves the original data while you check the cleaned results. With the source text in A2, enter =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) in B2 and fill it down. SUBSTITUTE changes the nonbreaking space (character 160) to an ordinary space, and TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. Review the results before using the helper column for counting; cleanup can change internal spacing too.

If the uncleaned text is in A2:A100 and the target is Apple, this Microsoft 365 formula can count values after removing ordinary and nonbreaking spaces: =SUMPRODUCT(--(TRIM(SUBSTITUTE(A2:A100,CHAR(160)," "))="Apple")). It returns 3 if exactly three cells reduce to Apple after that cleanup. For a workbook that must remain easy to audit, a filled-down helper column plus a regular COUNTIF is often clearer.

Check numbers stored as text

A cell can display 125 while storing the characters “125” as text. This often happens after importing a CSV file, copying data from a web page, or applying a text format before entry. Text and numeric values may behave differently in comparisons and exact-match counts, so first check the cells’ alignment, number format, and any warning indicator Excel displays. Formatting a text value as Number does not necessarily convert its underlying contents.

If the whole column should contain numbers, make a copy or use a helper column to verify the conversion before replacing source values. Depending on the import, selecting the affected range and using Data > Text to Columns, then choosing Finish, can convert number-like text to numeric values. Another option is to use the warning icon’s Convert to Number command when Excel offers it. Then test the count again and confirm that the range contains real numbers, not just values that look numeric.

Also check what “counting properly” means. =COUNTIF(B2:B100,">100") counts values above 100, whereas a criterion of ">=100" includes 100. If numbers are mixed with labels such as N/A, determine whether those labels should be excluded rather than assuming every nonblank cell is numeric.

Use the right method for dates and times

Excel stores genuine dates as serial numbers, even though cells display them as dates. A date criterion can therefore fail if the cells contain text that merely looks like a date, or if the criterion is built from an ambiguous date string. For reliable date boundaries, use DATE to construct the date rather than relying on regional interpretations of text such as 1/2/2026.

With real dates in C2:C100, this formula counts dates on or after January 1, 2026: =COUNTIF(C2:C100,">="&DATE(2026,1,1)). It returns 12 if 12 cells in that range contain dates on or after that day. If you need a particular month, use a start date inclusive and the next month’s start exclusive. With the same date range, =COUNTIFS(C2:C100,">="&DATE(2026,1,1),C2:C100,"<"&DATE(2026,2,1)) counts January 2026 dates and excludes February 1. It returns 7 if seven dates fall in that interval.

Times can cause a related surprise: a cell containing a date plus a time is later than the date serial at midnight. A criterion for exactly one date may not behave like a whole-day test when the entries include times. Use a lower boundary for the date and an exclusive upper boundary for the following day, as in the month example.

Understand wildcards and exact matches

COUNTIF supports wildcards in text criteria. An asterisk (*) matches any sequence of characters, including no characters, and a question mark (?) matches one character. For example, with descriptions in A2:A20, =COUNTIF(A2:A20,"*east*") counts cells containing east anywhere in the text. It returns 4 if four cells match. This is useful for searching, but it can count more than intended: “northeast,” “East office,” and “least” all contain the sequence east if case is ignored.

If the text you want to find literally contains an asterisk or question mark, put a tilde (~) before that character in the criterion to treat it as a literal. For instance, a criterion containing ~* searches for an actual asterisk rather than using it as a wildcard. When a result seems too high, check whether the criterion accidentally includes wildcards or whether a broad wildcard is matching partial words.

COUNTIF does not distinguish uppercase from lowercase. If a task specifically requires a case-sensitive count, COUNTIF alone is not the appropriate function; a formula using EXACT with SUMPRODUCT can be used instead. That is a different requirement from a normal exact-text match, where exact means matching the whole cell value rather than matching its case.

Verify the range, recalculation, and workbook state

Confirm that the range covers the rows you intend to count. A formula pointed at A2:A100 will not include a new entry in A101. Similarly, a copied formula may have shifted its range. If the formula should keep the same range when filled, use absolute references: with source items in A2:A100 and a criterion in D2, =COUNTIF($A$2:$A$100,D2) keeps the source range fixed as the formula is copied down.

COUNTIF requires its range and criterion arguments; a misspelled range or a reference to the wrong worksheet can produce an unexpected result or an error. If the formula was copied from another program or region, check the argument separators too. Microsoft 365 for Windows commonly displays commas in formulas, as shown here, but Excel’s list separator can depend on Windows regional settings. Excel may require semicolons in a locale configured that way.

If the formula appears correct but its displayed result is stale, check Formulas > Calculation Options and make sure calculation is set to Automatic if you expect immediate updates. Press F9 to recalculate formulas in an open workbook. On a Mac, use the Formulas tab for calculation settings; keyboard shortcuts can differ by keyboard and Excel version. Also check that the cell containing the formula is not formatted as Text. If it is, change the format to General and re-enter the formula.

When COUNTIFS or another approach is better

COUNTIF handles one criterion. If you need several conditions at the same time—for example, count rows where the region is West and the status is Complete—use COUNTIFS. With regions in A2:A100, statuses in B2:B100, and those exact values required, use =COUNTIFS(A2:A100,"West",B2:B100,"Complete"). It counts rows meeting both conditions, not rows meeting either one. Make sure each criteria range covers the same rows.

If the task is to count visible, filtered rows only, COUNTIF is not designed to do that. If the task needs case-sensitive matching, counting distinct values, or evaluating a more complicated condition, choose a method suited to that requirement rather than piling extra wildcard characters into COUNTIF. For routine one-condition counting, however, COUNTIF is usually the simplest choice.

Frequently Asked Questions

Why does COUNTIF return zero when the text looks identical?
Look for leading or trailing spaces, nonbreaking spaces, and other imported characters. Compare the formula’s criterion with the actual cell contents, and try a cleaned helper column if the values came from a web page or CSV.

Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase letters as equivalent. If capitalization must match, use a case-sensitive method such as combining EXACT with SUMPRODUCT.

Does COUNTIF count hidden or filtered rows?
Yes. COUNTIF includes matching cells in hidden rows and rows hidden by a filter. Its result does not automatically change to count only the visible records.

Why does COUNTIF not count a date that is displayed correctly?
The displayed value may be text rather than a genuine Excel date, or it may include a time component. Check the underlying data and use date boundaries built with DATE when you need a reliable period count.

Can COUNTIF count more than one condition?
Not in a single COUNTIF criterion. Use COUNTIFS for multiple conditions that must all be true, and ensure the criteria ranges have matching dimensions.

A quick troubleshooting order

To find the cause without changing good data, work through these checks in order:

Confirm the range includes every intended row and the criterion points to the right value.
Check whether the criterion needs quotes, a comparison operator, or a reference joined with &.
Inspect matching and nonmatching cells for spaces, hidden characters, text-formatted numbers, and text-formatted dates.
Check for wildcard characters and remember that matching is not case-sensitive.
Decide whether hidden rows should count, then verify calculation settings if the result appears stale.

This process separates a formula-syntax problem from a data-quality problem. Once the criterion, range, and underlying values agree, COUNTIF should return a predictable count; if the requirement involves multiple conditions, visible rows, or case sensitivity, switch to the function or method built for that requirement.

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.