Excel COUNTIF Wrong Count? Fixes & Causes

Formulas & functions · Updated November 22, 2026

If Excel’s COUNTIF is returning an incorrect count, the cause is almost always one of four things: criteria syntax (text vs. numbers, wildcards), data type mismatches (numbers stored as text), the function’s case-insensitivity, or a range that includes hidden/duplicate cells. The fix starts with checking how the criteria is written and what the data actually contains.

How COUNTIF Actually Works

COUNTIF(range, criteria) counts cells in a range that meet a single condition. The range can be a contiguous block like A1:A100, a named range, or a whole column (A:A). The criteria can be a number, text, an expression (like “>5”), or a cell reference. A critical detail: when you use a comparison operator, the operator and value must be enclosed in double quotes, and the cell reference is joined with an ampersand. For example, to count values greater than the number in D1, write =COUNTIF(A2:A20,">"&D1). Without the ampersand, Excel treats the reference as literal text, which rarely matches anything.

Another subtlety: text criteria are case-insensitive. COUNTIF(“Apple”) will count “apple”, “APPLE”, and “ApPlE”. If you need a case-sensitive count, you must use SUMPRODUCT with EXACT, like =SUMPRODUCT(--EXACT(A2:A20,"Apple")).

Common Causes of Wrong Counts

  • Numbers stored as text: If your range contains numbers formatted as text (often marked with a green triangle), COUNTIF with a numeric criterion may miss them. Use =COUNTIF(A2:A20,"*"&B1&"*") only if you want partial matches, but for exact numeric matches, convert text to numbers with VALUE or Text to Columns.
  • Wildcards: The asterisk (*) matches any sequence, the question mark (?) matches any single character, and the tilde (~) escapes them. If you want to count cells that literally contain an asterisk, use =COUNTIF(A2:A20,"~*").
  • Leading/trailing spaces: A cell with ” Apple” (space before) won’t match “Apple”. Use TRIM on the data or criteria.
  • Hidden rows: COUNTIF counts all cells in the range, including rows hidden by filtering or manually hidden. If you need to count only visible rows, use SUBTOTAL with function_num 103 (COUNTA) or 102 (COUNT), but those don’t handle criteria directly—use SUMPRODUCT with SUBTOTAL.
  • Duplicate criteria references: If your criteria cell contains a formula that returns text, make sure it doesn’t include extra characters.

Diagnosing the Problem Step by Step

  1. Select the range and check the status bar for a quick count of numbers (if your data is numeric). Compare that to your COUNTIF result.
  2. Use the ISNUMBER function on a sample cell: =ISNUMBER(A2). If it returns FALSE, the cell is text.
  3. Check for invisible characters by using =LEN(A2) and compare to the visible length.
  4. Test your criteria alone in a cell: type =COUNTIF(A2:A20,"Apple") and then =COUNTIF(A2:A20,A2) where A2 is a known value.
  5. Use Excel’s Evaluate Formula tool (Formulas tab > Formula Auditing > Evaluate Formula) to see how the criteria is being interpreted.

Fixes for Specific Scenarios

Scenario 1: Counting exact matches with text that contains wildcards. If you need to count cells equal to “A*B” literally, use =COUNTIF(A2:A20,"A~*B"). The tilde tells Excel the next character is literal.

Scenario 2: Counting numbers greater than a threshold but the threshold is a cell reference. Use =COUNTIF(A2:A20,">"&B1). If B1 contains 10, this counts values >10. If you write “>B1” without the ampersand, it looks for text that is literally “>B1”, which matches nothing.

Scenario 3: Counting dates. Dates are stored as serial numbers in Excel. If you write =COUNTIF(A2:A20,"1/1/2025"), it may work if the cell is formatted as a date, but safer to use =COUNTIF(A2:A20,DATE(2025,1,1)) or reference a cell containing the date.

Scenario 4: Counting blanks or non-blanks. Use =COUNTIF(A2:A20,"") for blanks and =COUNTIF(A2:A20,"<>") for non-blanks. Note that "<>" counts cells that are not empty, including cells with formulas that return “”.

Alternatives When COUNTIF Isn’t Enough

If you need multiple criteria, use COUNTIFS. For case-sensitive counts, use SUMPRODUCT with EXACT. For counting unique values, use =SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20)) (but beware of division by zero if blanks exist). For counting with OR logic, you can add two COUNTIFs: =COUNTIF(A2:A20,"Red")+COUNTIF(A2:A20,"Blue"). For counting based on a condition in another column, use COUNTIFS with multiple ranges: =COUNTIFS(A2:A20,"Yes",B2:B20,">10").

If you’re using Excel 365, consider FILTER and COUNTA: =COUNTA(FILTER(A2:A20,(A2:A20="Apple")*(B2:B20>10))). This is more flexible but requires dynamic arrays.

Preventing Errors with Data Types

The most common reason COUNTIF returns a wrong count is that numbers are stored as text. This happens when data is imported from a CSV, copied from a website, or entered with an apostrophe. To fix, select the range, click the warning icon, and choose “Convert to Number”. Alternatively, use =VALUE(A2) in a helper column. For text that should be numbers, also check for non-breaking spaces (character 160) which look like spaces but aren’t trimmed by TRIM. Use =SUBSTITUTE(A2,CHAR(160),"") to remove them.

Another prevention: when building formulas, use structured references in Excel Tables (e.g., Table1[Column1]) to avoid range misalignment. If you insert rows, a fixed range like A2:A20 won’t expand, but a table reference will.

Frequently Asked Questions

Why does COUNTIF return 0 when I see the value in the cell? Most likely the cell contains text that looks like a number, or there are hidden spaces. Use ISNUMBER to check, and TRIM to clean.

Can COUNTIF count cells with partial text? Yes, use wildcards: =COUNTIF(A2:A20,"*apple*") counts any cell containing “apple”. To count cells starting with “apple”, use “apple*”.

Does COUNTIF count cells with formulas that return empty strings? Yes, a formula like =IF(1=2,"","x") returns “x”, but if it returns “”, that cell is counted as non-blank because it contains a formula. To exclude those, use =COUNTIF(A2:A20,"?*") to count cells with at least one character.

Why does COUNTIF count more than the visible filtered rows? Because COUNTIF ignores filters. Use SUBTOTAL(103, range) to count visible non-empty cells, but for criteria, you need a helper column or SUMPRODUCT with SUBTOTAL.

How do I count cells that are not equal to a value? Use =COUNTIF(A2:A20,"<>Apple"). Note that this counts blanks as not equal, so if you want to exclude blanks, use =COUNTIF(A2:A20,"<>Apple")-COUNTIF(A2:A20,"").

Is COUNTIF case-sensitive? No. For case-sensitive counting, use =SUMPRODUCT(--EXACT(A2:A20,"Apple")).

Related

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.