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
TRIMon 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
- JavaScript String split(): Syntax, Examples, and Tips
- Python r Strings: Raw String Meaning, Rules, and Examples
- Strings in R: Text, Character Vectors, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIFS Multiple Criteria Match?Yes, Excel’s COUNTIFS function can match multiple criteria across different columns (or the same column) using AND logic—all…
- Excel Unique Values Without Duplicates: Methods That WorkTo show each value once in Excel, use the UNIQUE function: with a list in A2:A7 containing Apple,…
- How to Share an Outlook Calendar With the Right PermissionsTo share an Outlook calendar, open Calendar, choose Share Calendar, enter the recipient’s email address, set what they…
- PMT in Excel: Calculate Loan Payments Step by StepTo calculate a fixed loan payment in Excel, use the PMT function: enter the interest rate per payment…
- Excel SUMPRODUCT Shortcut: Formula, Uses, and ExamplesExcel has no dedicated keyboard shortcut for SUMPRODUCT: enter the formula in a cell and press Enter. For…
- Excel MAX of Two Values: Formula and ExamplesTo find the larger of two values in Excel, use =MAX(A1,B1), replacing A1 and B1 with the cells…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.