Excel COUNTIF for Text: Exact Match Formula

Formulas & functions · Updated November 22, 2026

To count cells in Excel that equal a specific text exactly, use =COUNTIF(range,"text"). For example, =COUNTIF(A2:A10,"Apple") counts every cell in A2:A10 that contains only the word “Apple” (case-insensitive, no partial matches). This is the direct answer; the rest of this article explains how it works, common pitfalls, and alternatives.

How COUNTIF Handles Text Matching

COUNTIF is a core Excel function that counts cells meeting a single condition. When the criteria is a text string, Excel compares the entire cell content against that string. Key behaviors:

  • Case-insensitive: “apple”, “APPLE”, and “Apple” all match. To force case-sensitive matching, use SUMPRODUCT(--EXACT(range,"text")).
  • Exact whole-cell match: A cell containing “Apple pie” will NOT match “Apple”. Only cells that are exactly “Apple” (with no extra characters) count.
  • Leading/trailing spaces matter: A cell with a space before or after “Apple” (e.g., ” Apple”) will not match. Use TRIM on data or criteria if needed.
  • Wildcards: Use * (any sequence) and ? (single character) for partial matches. For example, =COUNTIF(A2:A10,"*Apple*") counts cells containing “Apple” anywhere.

Setting Up the Formula Correctly

Assume you have a list of fruit names in cells A2:A20. To count how many cells equal “Banana”:

  • Click an empty cell, e.g., C2.
  • Type =COUNTIF(A2:A20,"Banana")
  • Press Enter. The result shows the count.

If the text you want to match is in another cell (say B2), reference that cell instead of typing the text: =COUNTIF(A2:A20,B2). This is useful when the criteria changes frequently.

Using Cell References and Concatenation

Sometimes you need to build a criteria string dynamically. For instance, to count cells that equal the value in D2 plus a suffix, use the ampersand (&) to concatenate: =COUNTIF(A2:A20,D2&"-final"). This counts cells exactly equal to whatever D2 contains followed by "-final".

For a completely dynamic exact match, simply reference the cell: =COUNTIF(A2:A20,D2). No quotes are needed when using a cell reference because Excel interprets the reference as the criteria value.

Common Problems and Solutions

  • Problem: Formula returns 0 but you see the text. Check for invisible spaces or non-breaking spaces (often from web copy). Use =TRIM(A2) in a helper column, or use =COUNTIF(A2:A20,"*"&D2&"*") as a workaround, but that counts partial matches.
  • Problem: Case-sensitive counting. Use =SUMPRODUCT(--EXACT(A2:A20,"Apple")) (this is an array formula but works without Ctrl+Shift+Enter in modern Excel).
  • Problem: Counting cells that are blank or contain empty string. =COUNTIF(A2:A20,"") counts truly blank cells, but not cells with a formula returning "". Use =COUNTIF(A2:A20,"*") for non-empty text, or =SUMPRODUCT(--(LEN(A2:A20)>0)) for all non-empty cells.
  • Problem: Criteria longer than 255 characters. COUNTIF fails. Use =SUMPRODUCT(--(A2:A20=D2)) where D2 holds the long text.

Comparing COUNTIF, COUNTIFS, and SUMPRODUCT

COUNTIF handles a single condition. If you need to count cells that equal a text AND meet another condition (e.g., equal "Apple" in column A and have a value >5 in column B), use COUNTIFS: =COUNTIFS(A2:A20,"Apple",B2:B20,">5"). COUNTIFS is available in all Excel versions since 2007.

SUMPRODUCT is a more flexible alternative that can handle case-sensitive matching and array operations without wildcard limitations. For example, to count cells equal to D2 (case-insensitive) but ignoring errors, use =SUMPRODUCT(--(IFERROR(A2:A20,"")=D2)) (array formula, enter with Ctrl+Shift+Enter in older Excel).

Excel Versions and Compatibility

COUNTIF works identically in Excel for Microsoft 365, Excel 2021, 2019, 2016, and earlier versions on Windows and Mac. There is no difference in syntax. On Excel for the web, COUNTIF also works the same. The only version-specific note: if you use dynamic array functions like FILTER, those require Excel 2021 or Microsoft 365, but COUNTIF itself is universal.

Frequently Asked Questions

Does COUNTIF match partial text by default?

No. By default, COUNTIF matches the entire cell content. To count partial matches, you must use wildcards, e.g., =COUNTIF(A2:A20,"*Apple*") counts cells that contain "Apple" anywhere.

How do I count cells that equal text but ignore case?

COUNTIF is already case-insensitive. So =COUNTIF(A2:A20,"apple") counts "Apple", "APPLE", etc. If you need case-sensitive, use =SUMPRODUCT(--EXACT(A2:A20,"apple")).

Why does my COUNTIF return 0 when the text looks identical?

Most likely there are invisible characters like spaces, line breaks, or non-breaking spaces. Use =LEN(A2) to see the character count. Use =TRIM(A2) to remove extra spaces, or use =CLEAN(A2) to remove non-printable characters.

Can I count cells that equal text from a list of multiple values?

Yes, use SUMPRODUCT with COUNTIF: =SUMPRODUCT(COUNTIF(A2:A20,{"Apple","Banana"})) counts cells that equal either "Apple" or "Banana". This works as an array constant in all versions.

Does COUNTIF work with numbers stored as text?

Yes, but be careful. If a cell contains the number 5 as text (e.g., "5"), =COUNTIF(A2:A20,"5") will count it. If the cell is a real number, =COUNTIF(A2:A20,5) works. To avoid confusion, ensure data types are consistent.

How do I count cells that are not equal to a specific text?

Use the not-equal operator: =COUNTIF(A2:A20,"<>Apple") counts all cells that are not exactly "Apple", including blanks. To exclude blanks, use =COUNTIFS(A2:A20,"<>Apple",A2:A20,"<>").

Alternatives for More Complex Matching

If you need to count cells that equal text but also ignore leading/trailing spaces without cleaning the data, use =SUMPRODUCT(--(TRIM(A2:A20)=D2)) (array formula). For counting based on a case-insensitive exact match but with wildcard support, consider =COUNTIF(A2:A20,EXACT(A2:A20,D2)) – but that's not valid syntax; instead use =SUMPRODUCT(--(EXACT(A2:A20,D2))).

For counting cells that equal text after removing all spaces, use =SUMPRODUCT(--(SUBSTITUTE(A2:A20," ","")=SUBSTITUTE(D2," ",""))).

Remember: always verify your data for hidden characters. Use the LEN function to check character counts, and use TRIM and CLEAN to sanitize imported data.

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.