To count cells that match text in Excel, use COUNTIF: with the text values in A2:A10, =COUNTIF(A2:A10,"Apple") counts cells equal to Apple, ignoring capitalization. For a partial match, use asterisk wildcards, as in =COUNTIF(A2:A10,"*apple*"). The examples below explain exact and partial matches, wildcard rules, common snags, and alternatives.
What COUNTIF does with text
COUNTIF counts cells in a range when each cell meets one condition. Its syntax is COUNTIF(range,criteria): the range is where Excel looks, and the criteria tells it what to count. The criteria can be typed directly into the formula as text in quotation marks, or supplied by a cell reference.
For a small example, imagine A2:A10 contains a list of fruit names. If three cells contain Apple, a criterion of Apple returns 3. The match is not case-sensitive: Apple, APPLE, and apple all meet the same criterion. COUNTIF is therefore useful for ordinary text counts, but it cannot distinguish capitalization on its own.
The range and criterion need to be kept conceptually separate. A2:A10 tells Excel where to search; the text criterion tells it what to look for. If the criterion is in D2, referencing that cell makes it easier to change the target without editing the formula. This is especially useful for summaries where someone will choose a name, category, or status from a cell.
Count an exact text match
An exact text criterion counts a cell when its contents match the requested text, rather than merely containing those letters as part of a longer phrase. With fruit names in A2:A10, this formula counts cells whose value is Apple:
=COUNTIF(A2:A10,"Apple")
If Apple appears in three cells, the result is 3. A cell containing Green Apple does not count, because the whole cell value is not Apple. Capitalization does not change the result, but extra characters and spaces can: Apple␠ (with a trailing space) is not the same text as Apple for an exact match.
To make the criterion editable, put the desired word in D2. With fruit names in A2:A10 and the target text Apple in D2, this returns 3 if Apple occurs three times:
=COUNTIF(A2:A10,D2)
Because D2 is a reference rather than text typed inside the formula, do not put quotation marks around the reference. If the cell contains a value typed with extra spaces, the criterion may not match the list as expected.
Count text that contains a word or phrase
For a partial match, use the asterisk wildcard. An asterisk represents any number of characters, including no characters. With descriptions in A2:A10, the following counts cells containing the word apple anywhere in the text:
=COUNTIF(A2:A10,"*apple*")
If four descriptions contain apple, including entries such as Green Apple and apple pie, the result is 4. The wildcard at the beginning allows any characters before the word; the one at the end allows any characters after it. Since COUNTIF ignores case, this also finds Apple or APPLE.
Wildcard matching is a text search, not a word-boundary search. For example, a search for *art* also matches the letters art inside a longer word such as partial. If you need to match a complete word only, COUNTIF by itself may not be sufficient; consider separating words into fields or using a more tailored formula.
A wildcard can also be attached to a cell reference. With descriptions in A2:A10 and the search word in D2, this counts cells containing the text in D2:
=COUNTIF(A2:A10,"*"&D2&"*")
If D2 contains apple and four entries contain that text, the result is 4. The ampersands join the wildcard characters to the cell value. In HTML, the ampersands shown in this article are written as & so the formula displays correctly; in Excel, type the ordinary ampersand character in the formula.
Use question marks and match wildcard characters literally
The question mark is another COUNTIF wildcard. It stands for exactly one character, while an asterisk stands for zero or more characters. With codes in A2:A10, the criterion A?e matches three-character entries that start with A and end with e, such as Abe or Axe. It does not match a longer value such as Able.
Sometimes the data itself contains an asterisk, question mark, or tilde and you want to find that character literally. In COUNTIF criteria, a tilde escapes a wildcard. With product codes in A2:A10, this counts cells whose text is a literal asterisk:
=COUNTIF(A2:A10,"~*")
To look for a literal question mark, use ~? in the criterion. To look for a literal tilde, double it as ~~. The escape character matters when a search seems unexpectedly broad: a bare asterisk means “any text,” not “the star symbol.”
Build criteria from cells or add conditions
You can combine a text criterion with a cell reference when you need a changing partial search. You can also use COUNTIFS when the count depends on more than one condition. For example, suppose names are in A2:A10 and departments are in B2:B10. If you want to count rows where the name is Maya and the department is Sales, use:
=COUNTIFS(A2:A10,"Maya",B2:B10,"Sales")
If two rows meet both conditions, the result is 2. COUNTIFS applies the conditions together: a row must satisfy every supplied condition to count. Its corresponding ranges should cover the same number of rows. COUNTIF, by contrast, handles just one range and one criterion.
For a criterion that includes an operator, join the operator and the cell value. For instance, if dates are in C2:C10 and D2 contains a cutoff date, this counts dates after the value in D2:
=COUNTIF(C2:C10,">"&D2)
The result depends on the dates in the range and the date stored in D2. This pattern is useful for operators such as greater than or less than, but for text counting the usual pattern is simply a direct text criterion or wildcards around a search term. Check that date cells contain real Excel dates, not text that merely looks like a date.
Know what COUNTIF does not distinguish
COUNTIF text matching is case-insensitive. If a list contains Apple, APPLE, and apple, a criterion of Apple counts all three. That is normally convenient for categories and names, but it can be wrong when capitalization carries meaning, such as a case-sensitive identifier.
For a case-sensitive count, the EXACT function can compare each cell with the target text, and SUMPRODUCT can add the TRUE results. With values in A2:A10, this counts only cells whose capitalization exactly matches Apple:
=SUMPRODUCT(--EXACT(A2:A10,"Apple"))
If the range contains Apple, APPLE, and apple once each, the result is 1. EXACT is case-sensitive; the double unary converts its TRUE/FALSE results to 1s and 0s, and SUMPRODUCT adds them. This is a different approach from COUNTIF, so use it only when case distinctions matter.
Also note that numbers stored as text and numbers stored as numeric values are not always interchangeable in criteria. A cell that looks like 123 may contain text rather than the number 123. If a count misses apparently matching entries, check the underlying cell values and formats, and test a few cells directly. Formatting changes how a value looks; it does not necessarily change the value Excel stores.
Common reasons a text count is wrong
A COUNTIF result of zero, or a count that seems too high, often reflects a difference between what is visible and what is actually in the cell. Review these issues before replacing a formula:
- Leading or trailing spaces: Apple and Apple followed by a space are different for an exact match. Imported lists often contain such spaces.
- Hidden characters: Data copied from websites or other systems may contain nonprinting characters that are not obvious on screen.
- Wildcard behavior: A criterion containing
*or?searches patterns unless those characters are escaped with a tilde. - Partial matches:
*art*can match a longer word containing those letters, not just a separate word. - Wrong range: Confirm that the range includes all intended rows and does not accidentally include a heading or omit new records.
- Text-versus-number differences: A code or number stored as text may not behave like a numeric value in every comparison.
When spaces are the likely problem, inspect a suspicious cell by selecting it and looking in the formula bar. For a one-time cleanup, Excel’s TRIM function can remove ordinary extra spaces from text, but it does not remove every possible nonprinting character. Keep a copy of imported data before cleaning it, especially if the original values may be needed for auditing.
COUNTIF compared with other counting options
Choose COUNTIF when one condition is enough, such as counting a status or searching for a phrase. Use COUNTIFS for several conditions that must all be true, such as a specific text label in one column and a region in another. For multiple alternative words in one column, separate COUNTIF calculations can be added, but be careful: a cell that contains both search terms may be counted twice if the criteria are partial matches.
For example, adding a count for *red* to a count for *apple* can count “red apple” in both parts. If the goal is the number of cells matching either condition only once, the formula needs to account for overlap, or the data can be arranged so each cell has one standardized category. Thinking through whether the conditions mean “and,” “or,” or “contains either without duplicates” prevents a plausible-looking but misleading total.
Another practical alternative is to standardize the source data before counting. If categories are inconsistent, a dropdown list created with Data Validation can reduce spelling variations in new entries. For existing data, a helper column can normalize or classify values before you count them. COUNTIF is not a substitute for deciding what counts as the same category.
Frequently Asked Questions
Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase letters as equivalent. If capitalization must match exactly, use a case-sensitive approach such as SUMPRODUCT with EXACT.
How do I count cells that contain a word anywhere?
Put an asterisk before and after the word in the criterion. With text in A2:A10, =COUNTIF(A2:A10,"*apple*") counts cells containing apple anywhere in their contents, without regard to case.
How do I count cells that are exactly equal to text in another cell?
Reference the cell directly as the criterion. If the list is in A2:A10 and the target is in D2, use =COUNTIF(A2:A10,D2). The match is case-insensitive and does not ignore extra spaces.
Can COUNTIF count two text conditions at once?
COUNTIF takes one condition. Use COUNTIFS when a row must satisfy multiple conditions, such as a matching name in one column and a matching department in another. The criteria ranges must align in size.
Why does COUNTIF return zero when the text looks identical?
Check for hidden leading or trailing spaces, nonprinting characters, an incomplete range, or a mismatch between text and numeric data. For an exact match, even an extra space can make the cell different from the criterion.
How do I search for an actual asterisk in a cell?
Escape it with a tilde in the criterion. For a literal asterisk in cells in A2:A10, use =COUNTIF(A2:A10,"~*"). A bare asterisk is a wildcard for any number of characters.
Related
- Close Group Excel Shortcut: Close Workbooks and Rows
- Close Excel Shortcut: Windows and Mac Keys
- Checklist Template Excel: Set Up, Track, and Reuse Tasks
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sheet Formula List: Essential Functions and ExamplesAn Excel sheet formula list is a reference to formulas and functions you can use to calculate, summarize,…
- How to Copy Formulas in Excel by Dragging the Fill HandleTo copy a formula in Excel by dragging, select the cell with the formula, point to the small…
- How to Compare Two Columns in Excel With a FormulaTo compare two Excel columns row by row, enter =IF(A2=B2,"Match","Different") in C2, then fill the formula down. This…
- How to Add a Formula to an Entire Row in ExcelTo add a formula across a row in Excel, enter it in the first cell where you want…
- Excel DATEDIF Age Formula: Calculate Years, Months, DaysTo calculate someone’s age in completed years in Excel, use =DATEDIF(A2,TODAY(),"Y"), with the date of birth in A2.…
- Recursive LAMBDA in Excel: Examples That WorkRecursive LAMBDA formulas call themselves to repeat a calculation, such as finding a factorial or stepping through a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.