To count cells containing a particular text value in Excel, use COUNTIF: with the values to check in A2:A8, the formula =COUNTIF(A2:A8,"Apple") returns 2 if two cells contain Apple, regardless of capitalization. Use criteria such as "*apple*" to count text that contains a word, or "App*" to count text that begins with it.
What COUNTIF Does with Text
COUNTIF checks a range against one condition and returns the number of cells that meet it. Its basic structure is a range followed by a criterion. The criterion can be a text value, a pattern using wildcards, or a reference to a cell containing the criterion. It is useful for quick counts such as how many rows have a particular status, product, city, or category.
For a concrete example, imagine A2:A8 contains Apple, apple, Green apple, Pear, Apple, a blank cell, and Pineapple, in that order. Excel treats text matching as case-insensitive by default, so Apple and apple match the same text criterion. COUNTIF does not require the range to be sorted, and it does not change or filter the underlying data; it simply returns a count.
The criterion is not limited to words. It can also use wildcard characters, comparison operators, or a cell reference. The important distinction is whether you want an exact text match or a pattern match. A plain word such as Apple normally asks for an exact match, while wildcard characters expand the match to include other text.
Count an Exact Text Match
With the example values in A2:A8, use =COUNTIF(A2:A8,"Apple"). The result is 2: the first and fifth data cells contain Apple, and the lowercase apple also matches because COUNTIF ignores case. Green apple and Pineapple are not exact matches, even though they contain the letters apple.
Exact means the whole cell value must match the criterion, not that Excel checks capitalization. Extra characters matter. A cell containing Apple juice, for example, will not match Apple with this formula. Leading or trailing spaces also affect matching, so a value that looks like Apple may fail if the stored text is actually Apple followed by a space.
If the text to count is in another cell, use that cell as the criterion. With the same list in A2:A8 and the word Apple in D2, =COUNTIF(A2:A8,D2) returns 2. This is convenient when users need to change the search term without editing a formula. If D2 contains wildcard characters, Excel treats them as wildcards rather than ordinary punctuation.
Use Wildcards for Partial Text
Excel’s COUNTIF criteria support three wildcard characters. An asterisk represents any number of characters, including none; a question mark represents one character; and a tilde escapes a wildcard so it can be treated as a literal character. Wildcards are especially useful when names, labels, or descriptions contain varying text around the word of interest.
*matches any sequence of characters, such as zero or more letters.?matches exactly one character.~before an asterisk or question mark asks Excel to match that character literally.
With the example values in A2:A8, =COUNTIF(A2:A8,"*apple*") returns 5. The asterisk on each side allows characters before and after apple, so Apple, apple, Green apple, and Pineapple all qualify; Pear and the blank do not. This is a contains-text search, and it remains case-insensitive.
With the same range, =COUNTIF(A2:A8,"App*") returns 3 because it counts values beginning with App, whatever text follows. The formula =COUNTIF(A2:A8,"*apple") returns 5 for values ending in apple. A single question mark is useful when only one character may vary: for example, a criterion like AB? matches AB followed by one character, but not AB followed by two characters.
To count a cell whose actual value is an asterisk, assume C2:C5 contains *, A*, ?, and *; =COUNTIF(C2:C5,"~*") returns 2. The tilde makes the asterisk literal rather than the usual any-characters wildcard. For a literal question mark, use a tilde before the question mark in the criterion as well.
Build Criteria from Cell References
Cell references make COUNTIF formulas easier to reuse in worksheets and reports. Put a search term in one cell and point the criterion argument to it, as in the D2 example above. For a partial match, join a wildcard to the cell value using Excel’s ampersand operator. With names in A2:A8 and the search word apple in D2, =COUNTIF(A2:A8,"*"&D2&"*") returns 5.
The ampersands combine the wildcard characters with the contents of D2, forming a criterion equivalent to *apple*. If D2 changes to pear, the formula searches for pear instead. This is often more convenient than typing a new formula for each search. Keep in mind that a star typed into D2 will itself act as a wildcard; if users may enter wildcard characters as ordinary text, the criterion needs to escape them.
For a criterion that begins with a comparison operator, Excel can use that operator to compare values, so criteria are not always interpreted as literal text. When the goal is a text label, test the formula against representative values, especially if the label begins with symbols such as less-than or greater-than signs. For ordinary words and phrases, a direct criterion or a referenced cell is usually clear and reliable.
Count Text Cells and Combine Conditions
A common related question is how to count every text entry without specifying a particular word. With text values in A2:A8, =COUNTIF(A2:A8,"*") returns 6 for the example list because six cells contain text and one is blank. This is a useful quick count, but it is not the same as counting every non-empty cell: a range may also contain numbers, dates, or logical values, which are not text matches.
For more than one condition, use COUNTIFS, which applies multiple conditions across corresponding ranges. Suppose B2:B6 contains Open, Closed, Open, Open, and Open, while C2:C6 contains East, East, West, East, and West. With those layouts, =COUNTIFS(B2:B6,"Open",C2:C6,"East") returns 2: two rows have both an Open status and an East region. Each criteria range should be the same size and aligned row by row with the others.
COUNTIF accepts one condition; COUNTIFS is the more suitable choice when a text count depends on multiple fields, such as a department and a status. It counts rows meeting all supplied conditions, rather than adding counts for either condition. If the task is to count rows matching one of several alternative labels, separate COUNTIF formulas can be added, but make sure the categories do not overlap or the same cell may be counted more than once.
Case Sensitivity and Similar Functions
COUNTIF does not distinguish uppercase and lowercase letters. In the example range, a criterion of Apple counts both Apple and apple. This is normally desirable for categories and names, where capitalization is inconsistent. Changing the formula’s capitalization will not make the comparison case-sensitive.
If capitalization itself matters, use EXACT inside SUMPRODUCT. With the example values in A2:A8, =SUMPRODUCT(--EXACT("Apple",A2:A8)) returns 2 because two cells match Apple with that exact capitalization; the lowercase apple is excluded. EXACT compares text case-sensitively, and SUMPRODUCT totals the TRUE results. This is a different approach from COUNTIF and is most useful when case is meaningful in the data.
For a single text condition, COUNTIF is shorter and easier to maintain than a more elaborate array formula. Use COUNTIFS for several simultaneous conditions. Use a case-sensitive method only when the distinction is intentional, because it can make visually similar labels count separately. These functions are available in current Microsoft Excel versions, including Microsoft 365 for Windows.
Common Problems and Checks
When a count seems wrong, the issue is often the cell contents or the criterion rather than the function. Check the following before rebuilding the formula:
- Extra spaces: A cell may contain a leading, trailing, or repeated space. Inspect the cell in the formula bar; cleaning imported data may be necessary.
- Exact versus partial match: A plain criterion counts whole-cell matches. Add wildcards only when you intend to include longer text values.
- Wildcard characters in the data: A literal asterisk or question mark in a criterion needs a preceding tilde.
- Wrong range: Confirm that the range covers the intended rows and does not include a header or omit newer entries.
- Numbers stored as text: A cell that looks like a number may be stored as text. Decide whether the task is to count text or numeric values and check the cell format and contents.
- Formula-generated empty text: A formula returning an empty string is not always equivalent in every counting task to a genuinely empty cell. If those cells affect your result, test the range and choose a method that explicitly checks the cell values.
In Microsoft 365 for Windows, enter the formula in a cell and press Enter. Excel for Mac and Excel for the web use the same COUNTIF function and formula structure, though menu layouts and keyboard shortcuts differ. In installations configured for a region that uses semicolons as formula separators, Excel may display or require semicolons instead of commas; follow the separator used by other formulas in that workbook.
Frequently Asked Questions
Does COUNTIF for text care about uppercase and lowercase?
No. COUNTIF is case-insensitive, so Apple and apple match the same text criterion. If capitalization must match exactly, use a case-sensitive method such as EXACT combined with SUMPRODUCT.
How do I count cells that contain a word anywhere?
Put an asterisk before and after the word in the criterion. For example, with the data in A2:A8, =COUNTIF(A2:A8,"*apple*") counts cells that contain apple anywhere, including longer phrases.
Can COUNTIF count text that starts or ends with certain letters?
Yes. Put an asterisk after the starting text to match values that begin with it, or before the ending text to match values that finish with it. The asterisk stands for any number of following or preceding characters.
How do I count a literal asterisk or question mark?
Place a tilde immediately before the wildcard character in the criterion. For instance, ~* asks Excel to match an actual asterisk rather than treating it as a pattern.
Why does COUNTIF return zero when the text looks identical?
Check for hidden or extra spaces, a range that misses the cell, and whether you used an exact criterion when you meant a partial match. Also check whether the displayed value is produced by a formula or stored differently from what it appears to be.
Related
- How to Close the Queries & Connections Pane in Excel
- Cleaning Checklist Template Excel: Setup and Tips for Teams
- Close and Load in Excel: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Conditional Formatting in Excel With IF FormulasTo use an IF formula in Excel conditional formatting, select the cells, choose Home > Conditional Formatting >…
- Excel Sentence Case Formula: One Sentence or Many?For a cell in A2, use =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))) to capitalize the first character and make the rest lowercase. This…
- Calculating Hours Worked in Excel: Formulas & TipsTo calculate hours worked in Excel, put a shift’s start time in A2 and end time in B2,…
- Excel MID Function: Extract Text from the Middle of a CellExcel’s MID function extracts a chosen number of characters from the middle of a text string. Its syntax…
- Date Minus Days Formula in Excel: Examples and TipsTo subtract days from a date in Excel, use a minus sign: if A2 contains a date and…
- Excel Due Date Formula: Examples and Workday OptionsFor a basic due date in Excel, enter a start date in A2 and the number of calendar…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.