To count cells that contain particular text anywhere in their contents, use =COUNTIF(A2:A100,"*apple*"); the asterisks allow any characters before or after “apple.” For an exact text match instead, use =COUNTIF(A2:A100,"Apple"). COUNTIF is not case-sensitive, so both formulas treat uppercase and lowercase letters as equivalent.
How COUNTIF checks text
COUNTIF has two arguments: the range to examine and the condition each cell must meet. Its general syntax is =COUNTIF(range,criteria). The range is a group of cells, while the criteria can be text, a number, a comparison, or a text pattern using wildcards. Excel counts each matching cell once and returns a number.
For example, suppose A2:A6 contains “Red apple,” “Apple pie,” “green apple,” “Pineapple,” and “pear.” With those values in A2:A6, =COUNTIF(A2:A6,"*apple*") returns 4. The first three entries and “Pineapple” all contain the sequence apple. The formula counts matching cells, not the number of times the text appears: a cell containing “apple apple” still adds only 1.
COUNTIF is available in current Microsoft Excel for Microsoft 365 on Windows, Mac, and the web. The formula syntax is the same across those versions, although regional settings may require semicolons rather than commas between arguments. If Excel rejects a formula with commas, check the list-separator setting used by your installation.
Exact text versus text contained in a cell
An exact text criterion matches a cell’s complete contents, apart from capitalization. With “Apple” in A2 and “Apple pie” in A3, a criterion of “Apple” counts A2 but not A3. This is useful when entries are labels or category names and you do not want a longer phrase to count as the same value.
To search for a phrase as part of a cell’s contents, put an asterisk on both sides of the phrase. With descriptions in A2:A100, =COUNTIF(A2:A100,"*New York*") counts cells containing the consecutive words “New York,” including entries such as “New York office” and “Visit New York.” The search is for that phrase in that order; it does not count a cell containing “New, then York.”
For a single-character pattern, use a question mark. With product codes in A2:A100, =COUNTIF(A2:A100,"AB?12") counts entries such as “ABC12” and “ABX12,” but not “AB12” or “ABCD12.” A question mark represents exactly one character, while an asterisk can represent zero or more characters.
Use a cell as the search term
Hard-coding a word in a formula is simple, but a separate input cell makes the count easier to reuse. Suppose the text to look for is in E2 and the values to search are in A2:A100. With “apple” in E2, =COUNTIF(A2:A100,"*"&E2&"*") counts cells containing that text. The ampersands join the wildcard characters to the value in E2.
This setup lets you change E2 without editing the formula. It is handy for a small search box above a list, or for checking several keywords one after another. If E2 is blank, however, the criterion becomes two asterisks and can count text entries unexpectedly. To show a blank result until a search term is entered, with the same cell layout use =IF(E2="","",COUNTIF(A2:A100,"*"&E2&"*")). When E2 is empty this displays a blank; with “apple” in E2 it returns the count of matching cells.
Remember that a value in E2 containing an asterisk or question mark is treated as a wildcard, not necessarily as a literal character. If users can type any search term, this detail may affect results. The next section shows how to search for a wildcard character itself.
Search for literal asterisks and question marks
In COUNTIF criteria, an asterisk and a question mark have special meanings. To match one of those characters literally, place a tilde (~) immediately before it. For example, with file names in A2:A100, =COUNTIF(A2:A100,"*~**") counts text cells containing a literal asterisk anywhere. The first and last asterisks are wildcards; the middle tilde-asterisk pair means an actual asterisk.
With product descriptions in A2:A100, =COUNTIF(A2:A100,"*~?*") counts cells containing a literal question mark. The same escape character applies when a tilde itself is part of the text: use two tildes in the criterion to represent one literal tilde. These patterns are easy to misread, so test them against a few known examples before relying on the result.
Combine text with another condition
COUNTIF handles one condition. If the count must meet multiple conditions at once, use COUNTIFS. For instance, suppose descriptions are in A2:A100 and regions are in B2:B100. With “West” in column B as the required region, =COUNTIFS(A2:A100,"*apple*",B2:B100,"West") counts rows where the description contains “apple” and the region is “West.” Both conditions must be true for a row to be counted.
Each COUNTIFS range needs to cover the same number of rows and align with the other ranges. If A2:A100 is paired with B2:B99, the ranges do not have the same dimensions, and Excel may return an error. For a single text condition, COUNTIF is more direct; for two or more conditions, COUNTIFS usually makes the logic clearer than multiplying separate counts.
Case sensitivity and what counts as a match
COUNTIF does not distinguish uppercase from lowercase. A criterion of “apple” can match “Apple,” “APPLE,” or “aPpLe.” That behavior is usually helpful for ordinary words and names, but it is not suitable when capitalization has meaning, such as case-sensitive identifiers.
For a case-sensitive substring count, use SUMPRODUCT with FIND and ISNUMBER. Suppose A2:A5 contains “Apple,” “APPLE,” “Pineapple,” and “red Apple.” With that layout, =SUMPRODUCT(--ISNUMBER(FIND("Apple",A2:A5))) returns 2: “Apple” and “red Apple” contain the exact-case sequence, while “APPLE” and “Pineapple” do not. FIND is case-sensitive; SEARCH is its case-insensitive counterpart. This approach counts matching cells, not repeated occurrences within a cell.
Exact matching also does not mean that Excel ignores extra spaces or punctuation. A cell containing “Apple ” with a trailing space is not necessarily the same exact entry as “Apple,” and an exact criterion will not match “Apple pie.” If imported or copied data looks identical but produces an unexpected count, inspect the cell contents for spaces, nonprinting characters, or punctuation differences.
Common COUNTIF problems and fixes
- The formula returns zero: Confirm that the search phrase is spelled correctly and that the range points to the cells containing text. For a partial match, check that the criteria includes asterisks on both sides, such as
"*apple*". - Too many cells are counted: A partial match can find a sequence inside a longer word. “Apple” with wildcards also matches “Pineapple.” Use an exact criterion if the whole cell must equal the term.
- Text in a formula cell behaves unexpectedly: COUNTIF evaluates the displayed value returned by a formula. If that formula returns text, it can be counted; if it returns an empty string, it may not behave like a truly empty cell in every situation.
- There is an error or the formula is not accepted: Check that parentheses and quotation marks are balanced. Excel formulas need straight quotation marks, and some regional settings use semicolons instead of commas.
- The count changes after adding data: A fixed range such as A2:A100 does not include entries below row 100. Expand the range, or use a table column reference so new table rows are included automatically.
- Only some records seem to match: Look for inconsistent spacing, spelling, punctuation, or values stored in a different form. COUNTIF does not clean or standardize source data as it counts.
COUNTIF compared with other ways to count
Choose COUNTIF when you need one condition and want a count. Choose COUNTIFS when a record must satisfy several conditions, such as a text fragment plus a region or date. These functions return totals; they do not list the matching records. In Microsoft 365, FILTER can return rows meeting conditions when you need to inspect the records themselves, but it serves a different purpose from a count.
Another distinction is between counting cells that contain text and counting cells whose value is text. COUNTIF with a text pattern answers the first kind of question. For a general count of nonblank cells, COUNTIF is not always the right choice if cells contain formulas returning empty strings; choose a method suited to what “nonblank” means in your data. A count of a particular word, a count of all text entries, and a count of all nonempty-looking cells are related but different tasks.
For repeated analysis, consider converting a list into an Excel table using Insert > Table. A table expands as rows are added, helping avoid a formula range that stops short of new records. Tables do not change the way COUNTIF matches text; they simply make the source data easier to manage as it grows.
Frequently Asked Questions
How do I count cells that contain a word in Excel?
Use COUNTIF with an asterisk before and after the word. For text in A2:A100, =COUNTIF(A2:A100,"*word*") counts cells containing “word” anywhere in their contents. Replace “word” with the term you need.
Does COUNTIF distinguish between uppercase and lowercase?
No. COUNTIF is case-insensitive, so uppercase and lowercase versions of the same text match. For a case-sensitive substring count, use a method based on FIND, such as SUMPRODUCT with ISNUMBER.
How do I count an exact text match?
Use the text as the criterion without wildcards. For example, with values in A2:A100, =COUNTIF(A2:A100,"Apple") counts cells whose full contents are Apple, regardless of capitalization. It does not count “Apple pie.”
Can COUNTIF count text from another cell?
Yes. If E2 contains the search term and A2:A100 is the range, join wildcards to the cell reference: =COUNTIF(A2:A100,"*"&E2&"*"). This counts partial matches and updates when E2 changes.
Can I count cells containing either of two words?
For two separate partial-match counts, add two COUNTIF formulas, one for each word. Be aware that a cell containing both words will be counted twice in that sum. If each cell should count only once when either term appears, use a more tailored formula or helper column to avoid double-counting.
Related
- Combine Excel Sheets Into One Sheet?
- Closing Checklist Template Excel: Set Up and Use It
- How to Close the Queries & Connections Pane in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Excel Cell Value: Criteria, Examples & FixesTo make SUMIF use a value stored in a cell, put the cell reference in its criteria argument.…
- Change Text Filter to Number Filter in Excel?To change a Text Filters menu to Number Filters in Excel, convert the values in that column from…
- Excel XLOOKUP Between Two Dates: Formula and ExamplesTo use XLOOKUP for a date that falls between a start date and an end date, test both…
- Excel Filter Shortcut: How to Filter Data Quickly in ExcelIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn filters on or off for a selected…
- Excel Formula or Operator? Understand the DifferenceIn Excel, a formula is an expression that calculates a result, while an operator is a symbol that…
- Excel Growth Rate Formula: Calculate Change Over TimeTo calculate growth rate in Excel, divide the change between the new and old values by the old…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.