To count cells that match specific text in Excel, use =COUNTIF(A2:A10,"Apple"); to count cells that contain the text anywhere, use =COUNTIF(A2:A10,"*Apple*"). COUNTIF is not case-sensitive, and the asterisk wildcards let you choose whether the text must match the whole cell or only part of it.
What COUNTIF Does and How Its Criteria Work
COUNTIF counts cells in one range that meet one condition. Its syntax is =COUNTIF(range, criteria). The range is the cells Excel checks, and the criteria tells Excel what to look for. For a text criterion typed directly into a formula, put the text in double quotation marks.
For example, suppose A2:A8 contains Apple, apple, Green apple, Apple, Pear, Apple pie, and a blank cell, in that order. With this layout, the formula =COUNTIF(A2:A8,"Apple") returns 3. Excel counts the two cells containing Apple and the one containing apple because COUNTIF ignores capitalization. It does not count Green apple or Apple pie because those cells contain additional text.
Criteria can also use comparison operators or wildcard characters. Text comparisons with COUNTIF are generally case-insensitive, and the function evaluates each cell in the range against the criterion. It returns a count, not the matching text or the row numbers where matches occur.
Count an Exact Text Match
Use a text criterion without wildcards when the cell must match the specified text as a whole. With the example values in A2:A8 above, =COUNTIF(A2:A8,"Apple") counts cells whose content is Apple, regardless of capitalization. A cell containing Apple pie is not an exact match.
“Exact” here means the entire cell value matches the criterion, not that letter case must match. COUNTIF treats Apple, APPLE, and apple as equal for this purpose. Leading or trailing spaces are still part of a cell’s content, so a cell containing Apple followed by a space may not behave like a clean Apple entry. These extra spaces are a common reason a seemingly correct exact-match count is lower than expected.
To count a different word, replace Apple with the text you need, keeping the quotation marks. If the criterion contains a quotation mark itself, the formula needs Excel’s doubled-quotation-mark notation for that character. For ordinary product names, categories, and labels, a standard quoted criterion is usually all that is required.
Count Text That Appears Anywhere in a Cell
Use an asterisk before and after a word to match cells containing that word with other text around it. With the values in A2:A8 above, =COUNTIF(A2:A8,"*apple*") returns 5. It counts Apple, apple, Green apple, the second Apple, and Apple pie. The asterisk represents any number of characters, including no characters.
Wildcard placement changes what qualifies:
=COUNTIF(A2:A8,"Apple*")counts entries that start with Apple. In the example range, the result is 4: Apple, apple, Apple, and Apple pie.=COUNTIF(A2:A8,"*apple")counts entries that end with apple. In the example range, the result is 4: Apple, apple, Green apple, and Apple.=COUNTIF(A2:A8,"*app*")counts cells containing the letters app in that order, with any text before or after them.
These patterns are useful for searches in descriptions, notes, email addresses, and product names. They can also match more than you intended: *app* can find both application and appetizer, for example. Choose a distinctive fragment and inspect a few matching entries if an over-broad result would matter.
Use a Cell as the Text Criterion
A cell reference makes the search easy to change without editing the formula. Suppose the values are in A2:A10 and D2 contains Apple. With that layout, =COUNTIF(A2:A10,D2) returns the number of cells that exactly match the text in D2. If D2 changes to Pear, the formula counts Pear instead.
To use the text in D2 as a partial match, join wildcard characters to the reference with the ampersand operator. With search text in D2 and the list in A2:A10, =COUNTIF(A2:A10,"*"&D2&"*") counts cells containing the D2 text anywhere. The quotation marks enclose the wildcard characters, while the ampersands join them to the cell reference. This approach is helpful when a person enters search terms in a small input cell.
When combining a cell reference with an operator, join the operator and reference too. For example, to count entries not equal to the text in D2, the criterion can be written as "<>"&D2. Be careful when interpreting counts of “not equal”: blanks and other nonmatching values may affect the result. If you need to exclude blank cells as well, add that condition explicitly rather than assuming every cell in the range contains text.
Count Case-Sensitive Text
COUNTIF cannot distinguish Apple from apple. If capitalization is meaningful, use EXACT inside SUMPRODUCT instead. EXACT compares text with case sensitivity and returns TRUE for a match; the double unary operator converts TRUE and FALSE to 1 and 0 so SUMPRODUCT can add the results.
Suppose A2:A6 contains Apple, apple, APPLE, Green Apple, and Pear, and you want only the exact-case value Apple. With those values in A2:A6, =SUMPRODUCT(--EXACT("Apple",A2:A6)) returns 1. The formula does not count apple, APPLE, or Green Apple. To compare against a search cell instead, replace the quoted word with that cell reference, such as D2.
SUMPRODUCT with EXACT is useful when case changes meaning, such as when distinguishing case-sensitive codes. For routine category labels, COUNTIF is simpler and usually more appropriate. Keep the compared range to a sensible size; formulas that evaluate thousands of cells are easier to manage than whole-column array calculations in a workbook with many such formulas.
Combine Text with Another Condition
COUNTIF handles one criterion. When the count must satisfy two or more conditions, use COUNTIFS, which applies multiple criteria to corresponding ranges. For example, suppose A2:A7 contains product names and B2:B7 contains regions: Apple/East, Apple/West, Pear/East, Apple/East, Pear/West, and Apple/East. With products in A2:A7 and regions in B2:B7, =COUNTIFS(A2:A7,"Apple",B2:B7,"East") returns 3. It counts Apple rows whose region is East.
Each criteria range in COUNTIFS must have the same dimensions as the others. You can use a cell reference for either condition, or use wildcard text in a criterion where partial matching is needed. For instance, a product criterion of "*apple*" can count descriptions containing apple while another criterion restricts the region. COUNTIFS is a better fit than trying to combine separate counts when the required conditions must be true on the same row.
Common Problems and How to Fix Them
If a formula returns zero unexpectedly, first check the range and the spelling of the criterion. Look for leading or trailing spaces, inconsistent punctuation, and text that only looks alike. A nonbreaking space copied from a website, for example, may not behave like a regular space. COUNTIF does not remove or normalize such characters automatically.
- Check for hidden spaces. Click a suspect cell and inspect its contents in the formula bar. If data needs cleaning, use a helper column with TRIM for ordinary extra spaces; TRIM does not remove every special whitespace character.
- Check whether you need a partial match. An exact criterion for Apple will not count Apple pie. Add wildcards if the cell may contain surrounding text.
- Check wildcard meaning. An asterisk matches any sequence of characters; a question mark matches one character. These are patterns, not literal punctuation, unless escaped.
- Check the range boundaries. A formula using A2:A100 will not include a new entry placed in A101. Use an appropriately sized range or an Excel table column that expands as rows are added.
- Check numbers stored as text. A cell that looks like a number may be stored as text, which can affect criteria and comparisons. This is less common for plain words but matters in mixed data.
If you need to search for an actual asterisk or question mark rather than use it as a wildcard, precede it with a tilde. With the entries in A2:A10, the formula =COUNTIF(A2:A10,"~*") counts cells whose entire content is a literal asterisk. To find a literal asterisk anywhere in a cell, use =COUNTIF(A2:A10,"*~**"). A tilde itself is escaped by doubling it as ~~.
Another frequent misconception is that COUNTIF detects capitalization or searches only visible text. It does not distinguish letter case, and it evaluates cell values. If a cell displays text produced by a formula, COUNTIF can generally match the resulting text, but a different number format or hidden characters may make the displayed appearance misleading.
Choosing the Right Formula
Use the simplest pattern that matches the question being asked. For a category that should occupy the whole cell, use an exact criterion. For a word embedded in notes, use wildcards. For text whose capitalization matters, use EXACT with SUMPRODUCT. For conditions across multiple columns, use COUNTIFS.
- Exact text:
=COUNTIF(A2:A10,"Apple") - Text anywhere in a cell:
=COUNTIF(A2:A10,"*Apple*") - Text supplied in another cell:
=COUNTIF(A2:A10,D2) - Case-sensitive exact text:
=SUMPRODUCT(--EXACT("Apple",A2:A10)) - Text plus a second condition: use COUNTIFS with aligned ranges and criteria.
For a workbook used regularly, consider converting a clean list into an Excel table using Insert > Table. Structured references can make formulas easier to read and the table expands as data is added. The core COUNTIF method is the same in Excel for Microsoft 365 on Windows and Excel for Mac; ribbon placement and some interface details can differ, but these formulas do not require a special Windows-only feature. Excel for the web also supports COUNTIF.
Frequently Asked Questions
Does COUNTIF distinguish uppercase and lowercase text?
No. COUNTIF is case-insensitive, so Apple, APPLE, and apple count as matches for the same exact text criterion. Use SUMPRODUCT with EXACT when capitalization must match.
How do I count cells that contain a word, not just equal it?
Put an asterisk on both sides of the word in the criterion, as in =COUNTIF(A2:A10,"*Apple*"). The asterisks allow any characters before or after the word.
Can COUNTIF use a search term in another cell?
Yes. Use the cell reference as the criterion for an exact match, such as =COUNTIF(A2:A10,D2). For a partial match, join wildcard characters to the reference with ampersands.
Why does COUNTIF return zero when the text appears to match?
Check for extra spaces, unusual characters, a range that omits the relevant cells, or a need for wildcards. Also remember that an exact text criterion does not match a longer cell value such as Apple pie.
How do I count a literal asterisk in a cell?
Use a tilde to escape the wildcard. The criterion "~*" matches a cell that consists of an asterisk; "*~**" finds an asterisk anywhere within the cell.
Related
- Convert PowerPoint to Excel: Steps, Options, and Limits
- Access Database Import from Excel: Steps and Tips
- Excel Attendance Format: A Clear Monthly Attendance Tracker
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Exchange Rate Formula: Convert Currencies EasilyFor a basic Excel exchange rate formula, multiply the amount by the rate when the rate tells you…
- Excel Equation to Remove Duplicates: Formulas & StepsIn Microsoft 365, use =UNIQUE(A2:A100) to return a list of distinct values from A2:A100. This formula leaves the…
- Gross Margin Percentage Formula in Excel: A Clear GuideTo calculate gross margin percentage in Excel, subtract cost of goods sold (COGS) from revenue, then divide the…
- Excel VLOOKUP Name Errors: Causes, Fixes, and Real Examples#NAME? in an Excel VLOOKUP means Excel cannot recognize a name or text token in the formula; common…
- SUMIF Excel Cell Value: Criteria, Examples & FixesTo make SUMIF use a value stored in a cell, put the cell reference in its criteria argument.…
- Excel Present Value Formula: PV Function and ExamplesIn Excel, use the PV function to calculate the present value of future cash flows: it discounts them…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.