COUNTIF in Excel for Text: Examples and Tips

Formulas & functions · Updated November 22, 2026

Yes. Excel’s COUNTIF function counts cells that match text, either as an exact match or by a pattern. For example, with text in A2:A8, =COUNTIF(A2:A8,"Apple") counts cells equal to Apple, without distinguishing uppercase and lowercase; use wildcards such as * to find text inside longer entries.

What COUNTIF does

COUNTIF checks each cell in a range against one condition and returns the number of cells that meet it. Its syntax is =COUNTIF(range,criteria): the range is the cells to inspect, and criteria is the value or rule to match. The criteria can be text typed into the formula, a reference to another cell, or a pattern using wildcards.

For example, suppose A2:A8 contains Apple, apple, Pear, Apple, Green Apple, a blank cell, and pineapple. With those entries in A2:A8, =COUNTIF(A2:A8,"Apple") returns 3. COUNTIF treats the two capitalized entries and the lowercase entry as matches, but does not count Green Apple or pineapple as exact matches.

COUNTIF is useful for quick summaries: tallying survey responses, counting product names, checking how many records have a particular status, or measuring how often a label appears. It returns a count, not the matching text or the positions of matching cells.

Count exact text matches

For an exact text criterion, put the word or phrase in double quotation marks inside the formula. Excel compares the full cell content, so a criterion of Apple does not match Green Apple. The comparison is not case-sensitive: Apple and apple are treated alike.

With product names in A2:A8, =COUNTIF(A2:A8,"Pear") returns 1 for the example list above. If you want to avoid typing the criterion directly into a formula, place it in another cell. With the same list in A2:A8 and the word Apple in D2, =COUNTIF(A2:A8,D2) returns 3. The cell reference is not wrapped in quotation marks; Excel reads its contents as the criterion.

Exact matching can be affected by what is actually stored in a cell. A trailing space in Apple followed by a space is different from Apple for an exact match. If data was imported or copied from a website, invisible leading or trailing spaces may make a seemingly identical label fail to match. Check the source values before assuming COUNTIF is at fault.

Find text with wildcards

Wildcards let a text criterion match a pattern rather than one complete value. In Excel, an asterisk (*) stands for any number of characters, including none, and a question mark (?) stands for exactly one character. COUNTIF’s text matching is case-insensitive when wildcards are used, too.

  • "Apple*" matches text beginning with Apple, such as Apple and Applesauce.
  • "*apple" matches text ending in apple, such as pineapple.
  • "*apple*" matches text containing apple anywhere, such as Green Apple.
  • "B?ll" matches four-character strings such as Bell or Bill, with one character between B and ll.

With the example values in A2:A8, =COUNTIF(A2:A8,"*apple*") returns 4: Apple, apple, Green Apple, and pineapple all contain that sequence. This is a count of cells containing a match, not a count of how many times the sequence occurs inside each cell. A cell containing apple apple still contributes 1.

If the text you are searching for contains a wildcard character that should be treated literally, put a tilde (~) before it. For example, with entries in A2:A20, =COUNTIF(A2:A20,"~*") counts cells whose entire contents are a literal asterisk. Use ~? to match a literal question mark and ~~ to match a literal tilde. For a literal wildcard within a longer pattern, combine the escape with other characters.

Use a cell reference or build a criterion

Referencing a cell makes a formula easier to reuse: change the criterion in the referenced cell, and the result updates. It is also useful when you need to search for text that changes regularly or is entered by a user. To find a phrase anywhere within another cell’s text, join wildcard characters to the reference with the ampersand operator.

With text in A2:A8 and the search word apple in D2, =COUNTIF(A2:A8,"*"&D2&"*") returns 4 for the sample list. The ampersands join the asterisk before and after D2 to the word in that cell, creating a contains-this-text criterion.

Criteria can also include comparison operators such as greater than or not equal to. When combining an operator with a cell reference, put the operator inside quotation marks and join it to the reference. For example, with status text in A2:A20 and the criterion word in D2, =COUNTIF(A2:A20,"<>"&D2) counts cells whose contents are not equal to D2. It does not mean “contains a different word”; it tests whether the whole cell value differs. Be especially careful with criteria that use symbols, because a small change can alter whether Excel checks an exact value or a pattern.

Count text that meets multiple conditions

COUNTIF accepts one condition. When a record must meet two or more conditions at once, use COUNTIFS, which applies a condition to each of several corresponding ranges. For example, suppose A2:A7 contains Apple, Apple, Pear, Apple, Green Apple, Pear, while B2:B7 contains Open, Closed, Open, Open, Open, Closed. With those labels and statuses in those cells, =COUNTIFS(A2:A7,"Apple",B2:B7,"Open") returns 2: two rows have the exact Apple label and Open status.

COUNTIFS applies an AND test: a row must satisfy every condition to be counted. The ranges should cover corresponding rows and have the same dimensions. If you want to count one condition or another, you can add separate COUNTIF results when the groups do not overlap. If they can overlap, adding the results may count some cells twice, so consider the data structure before choosing that approach.

To count records where text contains a word and another column has a particular status, use wildcards in the text condition. With descriptions in A2:A20 and statuses in B2:B20, =COUNTIFS(A2:A20,"*refund*",B2:B20,"Open") counts rows whose description contains refund and whose status is Open. This is often more useful than trying to make one COUNTIF handle multiple columns.

When you need case-sensitive counting

COUNTIF does not distinguish uppercase from lowercase. If Apple and apple must be counted separately, use a formula built around EXACT, which compares text case-sensitively. For a single exact text value, with the entries in A2:A8, =SUMPRODUCT(--EXACT("Apple",A2:A8)) returns 2 for the sample list: only the two cells containing Apple with that capitalization qualify.

EXACT returns TRUE for each case-sensitive match and FALSE for each nonmatch. The double unary operator (--) converts those results to 1s and 0s, and SUMPRODUCT adds them. In Microsoft 365 for Windows, this formula can be entered normally in a cell; it does not require a special array-entry shortcut. It is more complex than COUNTIF, so use it only when capitalization is meaningful to the task. If case should not matter, COUNTIF is simpler and clearer.

Common COUNTIF problems and checks

A formula that returns zero when a match appears visible on the sheet often points to a mismatch between the displayed text and the stored value. Work through these checks before rewriting the formula:

  • Confirm that the range includes the intended rows and the criterion refers to the intended cell.
  • Look for extra spaces, nonbreaking spaces, punctuation differences, or slightly different wording in the source data.
  • Check whether the criterion needs wildcards. Apple will not match Green Apple, but *Apple* will.
  • Remember that COUNTIF ignores capitalization. Use EXACT with SUMPRODUCT if capitalization must matter.
  • When using a wildcard literally, escape it with a tilde; otherwise Excel interprets it as a pattern.
  • Make sure text criteria typed directly into a formula are enclosed in double quotation marks. A cell reference should not be quoted.

Another common misunderstanding is expecting a “contains” search from a plain text criterion. COUNTIF(A2:A20,Apple) is not a valid way to search for Apple within longer values; the criterion needs asterisks on both sides. Also, COUNTIF counts matching cells, not individual words or repeated appearances within a cell. If each cell contains a sentence and you need the number of occurrences of a word, that is a different problem and requires a different formula design.

For a reusable formula across a growing list, consider using an Excel Table so the data range can expand as rows are added. You can also use a normal range and update it when new records are entered. The right choice depends on how the workbook is maintained; the key is to ensure the range actually includes the records you want to count.

COUNTIF or another counting method?

Choose the simplest function that matches the question. COUNTIF is the straightforward option for one condition, including text patterns. COUNTIFS is appropriate for several simultaneous conditions across columns. SUMPRODUCT with EXACT is a practical option when a case-sensitive text count is required. These methods answer different questions, so switching functions will not fix a criterion that does not describe the intended match.

For a quick decision:

  • Use COUNTIF for one text criterion, whether exact or wildcard-based.
  • Use COUNTIFS when each counted row must meet multiple conditions.
  • Use SUMPRODUCT with EXACT when letter case matters for an exact comparison.
  • Use a separate method if the goal is to return matching rows or count repeated word occurrences inside cells rather than count matching cells.

In Microsoft 365 Excel for Windows, enter a formula in a worksheet cell and press Enter. The COUNTIF syntax and examples here also apply to Excel for Mac and Excel for the web; the interface differs, but the formulas do not. If your Excel uses a regional setting that requires semicolons as formula separators, replace the commas shown in the examples with the separator your installation expects.

Frequently Asked Questions

How do I count cells containing a word in Excel?
Use an asterisk on both sides of the word as the COUNTIF criterion. For example, with descriptions in A2:A20, =COUNTIF(A2:A20,"*late*") counts cells containing late anywhere in their text. The match is not case-sensitive.

Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase text as equivalent. For a case-sensitive exact match, use EXACT inside SUMPRODUCT, as shown above.

How do I count cells that begin with specific text?
Put the text before an asterisk in the criterion. For example, with names in A2:A20, =COUNTIF(A2:A20,"North*") counts cells whose text starts with North.

Can COUNTIF count two text conditions?
COUNTIF handles one condition. Use COUNTIFS when each row must meet two conditions, such as a specific product in one column and a status in another.

Why does COUNTIF return zero when the text looks the same?
Check for extra spaces, hidden characters, punctuation, a misspelled criterion, or a range that omits the cell. Also check whether you intended a partial match: an exact criterion will not match a longer phrase that merely contains the same word.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.