Excel COUNTIF Not Working? Fixes & Examples

Errors & fixes · Updated November 22, 2026

The direct answer: if your Excel COUNTIF formula is returning 0, the wrong count, or a #VALUE! error, it is almost always a syntax issue, a data type mismatch (text vs. number), or a wildcard character being interpreted literally. The formula itself is =COUNTIF(range, criteria), and once you ensure the range is correct and the criteria matches the data type, it works as expected.

What COUNTIF Actually Does

COUNTIF is a statistical function that counts cells within a single range that meet a single condition. It has been part of Excel since early versions and remains one of the most used functions for quick data analysis. The syntax is deceptively simple:

  • range – the group of cells to evaluate (e.g., A2:A100).
  • criteria – the condition that determines which cells to count, expressed as a number, text, expression, or cell reference.

For example, =COUNTIF(B2:B20, "Complete") counts how many cells in B2:B20 contain the exact text “Complete”. The function is case-insensitive, so “complete” and “COMPLETE” are treated the same.

Why COUNTIF Returns 0 (and How to Fix It)

The most common complaint is “COUNTIF returns 0 when I know there are matches.” Here are the usual culprits and their fixes:

  • Text vs. number mismatch: If your criteria is a number but the cells are stored as text (often indicated by a green triangle or left-aligned numbers), COUNTIF won’t match. Fix: use =COUNTIF(A2:A100, "123") (text) or convert the data to numbers using the Text to Columns wizard or the VALUE function.
  • Trailing/leading spaces: Invisible spaces in the data or in the criteria break exact matches. Fix: use =COUNTIF(A2:A100, "*"&D1&"*") with a wildcard, or clean data with TRIM.
  • Wildcard characters: The asterisk (*) and question mark (?) are wildcards. If you want to count literal asterisks, you must escape them with a tilde (~). Example: =COUNTIF(A2:A100, "~*") counts cells containing an asterisk.
  • Criteria as a cell reference: If you use =COUNTIF(A2:A100, D1) and D1 contains a formula that returns a number, but the data is text, it fails. Use =COUNTIF(A2:A100, "*"&D1&"*") for partial text matches.

Using Wildcards for Partial Matches

Wildcards are a powerful feature that many users overlook. COUNTIF supports three:

  • * (asterisk) – matches any sequence of characters. For example, =COUNTIF(A2:A100, "*apple*") counts cells containing “apple” anywhere.
  • ? (question mark) – matches any single character. =COUNTIF(A2:A100, "A?") counts cells like “A1”, “AB”, but not “A” or “ABC”.
  • ~ (tilde) – escapes the wildcard, so =COUNTIF(A2:A100, "~?") counts cells that literally contain a question mark.

Note that wildcards work with text criteria only. If you need to count numbers greater than a value, you must use comparison operators, not wildcards.

Counting with Comparison Operators (>, <, >=, <=, <>)

To count cells that meet a numerical condition, you embed the operator in the criteria as a text string. For example, to count sales over $500 in B2:B100:

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

For “not equal to”, use <>. For example, =COUNTIF(C2:C50, "<>Pending") counts all cells that are not “Pending”. If you want to count non-blank cells, use "<>" (not equal to empty). To count blank cells, use "" (empty string) or "=".

Common Errors and How to Avoid Them

  • #VALUE! error: This occurs when the range argument is not a valid range (e.g., you typed a single cell instead of a range, or the range includes errors). Fix: ensure the range is contiguous and correct.
  • Counting dates: Dates are stored as serial numbers. To count dates after a certain date, use =COUNTIF(A2:A100, ">"&DATE(2025,1,1)). If you put the date directly in quotes as text, it may not match.
  • Counting with multiple criteria: COUNTIF handles only one condition. For multiple conditions, use COUNTIFS. Example: =COUNTIFS(A2:A100, "West", B2:B100, ">100") counts rows where region is “West” and sales exceed 100.

COUNTIF vs. COUNTIFS vs. SUMPRODUCT

Many users wonder when to use each. Here’s a quick guide:

  • COUNTIF – one range, one condition. Simple and fast.
  • COUNTIFS – multiple ranges and conditions. Available in all modern Excel versions. Use when you need two or more criteria.
  • SUMPRODUCT – can count with complex logic, including OR conditions. For example, to count cells that are “Red” or “Blue”: =SUMPRODUCT(--((A2:A100="Red")+(A2:A100="Blue")>0)). This is more advanced but flexible.

Real-World Examples and Worked Calculations

Let’s walk through a practical example. Suppose you have a list of orders in A2:A100 with statuses (e.g., “Shipped”, “Pending”, “Cancelled”).

  1. Count “Shipped” orders: =COUNTIF(A2:A100, "Shipped"). If 30 cells contain “Shipped”, the result is 30.
  2. Count orders that are not “Cancelled”: =COUNTIF(A2:A100, "<>Cancelled"). If 10 are cancelled out of 99, result is 89.
  3. Count orders with statuses starting with “S”: =COUNTIF(A2:A100, "S*"). This counts “Shipped”, “Sent”, etc.

For a numeric example, with sales in B2:B100:

=COUNTIF(B2:B100, ">=1000") counts sales of $1000 or more. If 15 sales meet that, the result is 15.

Frequently Asked Questions

Why is my COUNTIF counting blank cells when I use “<>“? The criteria "<>" literally means “not equal to nothing”, which counts all non-blank cells. However, if a cell contains a formula that returns an empty string (“”), it is considered non-blank and will be counted. To exclude those, use =COUNTIF(A2:A100, "?*") which counts cells with at least one character.

Can COUNTIF count cells based on cell color? No, COUNTIF cannot evaluate formatting like fill color. You would need to use VBA or the SUBTOTAL function with filters. A common workaround is to add a helper column with a formula that returns a value based on the color, but that requires manual setup.

How do I count cells that contain a specific word but not as part of another word? Use wildcards carefully. =COUNTIF(A2:A100, "*apple*") counts “apple” and “pineapple”. To count only whole words, you’d need a more complex formula like =SUMPRODUCT(--(ISNUMBER(SEARCH(" apple ", " "&A2:A100&" ")))).

Why does COUNTIF return #NAME? error? This usually means you typed the function name incorrectly (e.g., “COUNTIF” vs “COUNTIFF”) or the function is not available in your Excel version (unlikely, as it’s been around since Excel 2007). Double-check spelling and ensure you’re not using a conflicting name.

Is COUNTIF case-sensitive? No, by default it is case-insensitive. If you need case-sensitive counting, use SUMPRODUCT with EXACT: =SUMPRODUCT(--EXACT(A2:A100, "Apple")).

Alternatives and Advanced Tips

If you find COUNTIF limiting, consider these modern alternatives:

  • COUNTIFS for multiple conditions (as mentioned).
  • FILTER (Excel 365) to extract matching rows, then use COUNTA to count them. Example: =COUNTA(FILTER(A2:A100, B2:B100>100)).
  • Dynamic arrays like UNIQUE to count distinct values: =COUNTA(UNIQUE(A2:A100)).
  • PivotTables for quick counts without formulas, especially for large datasets.

Remember, COUNTIF is a workhorse, but it’s not the only tool. For most everyday counting tasks, it’s the right choice—just ensure your data is clean and your criteria matches the data type.

Related

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.