COUNTIF Excel Formula for Text: Exact and Partial Matches

Formulas & functions · Updated November 22, 2026

To count cells containing text in Excel, use =COUNTIF(A2:A20,"apple") for an exact match, or =COUNTIF(A2:A20,"*apple*") when the text can appear anywhere in a cell. COUNTIF is not case-sensitive, and the asterisk (*) and question mark (?) let you count partial text matches.

How COUNTIF counts text

COUNTIF checks a range and counts the cells that meet one condition. Its syntax is =COUNTIF(range,criteria): the first argument is the cells to inspect, and the second is the condition to match. Put text criteria in straight double quotation marks. In Microsoft 365 for Windows, enter the formula in a cell and press Enter; Excel calculates the result and updates it when the referenced cells change.

For example, with department names in A2:A8, this formula counts cells whose value is exactly Sales:

With department names in A2:A8, enter =COUNTIF(A2:A8,"Sales"). If Sales appears in A2, A5, and A8, the result is 3.

The range can be a row, column, or rectangular block, but the criterion applies to each cell individually. COUNTIF accepts one condition. If you need to require two or more conditions at once, use COUNTIFS instead.

Count an exact text match

An exact text criterion counts a cell whose contents match the specified text, rather than a longer entry that merely contains it. For instance, the criterion "North" does not count "Northwind". Excel ignores capitalization in COUNTIF text comparisons, so North, NORTH, and north are treated as the same match.

With the following values in A2:A8—North, South, NORTH, East, Northwind, North, and a blank cell—this formula returns 3:

With those labels in A2:A8, enter =COUNTIF(A2:A8,"North"). The exact North entries count regardless of capitalization; Northwind does not.

You can also place the criterion in a cell, which is useful when someone needs to change the search term without editing a formula. If the search text is in D2 and the data is in A2:A100, use =COUNTIF(A2:A100,D2). Since the criterion is a cell reference, it is not enclosed in quotation marks. If D2 contains North, the formula counts North matches.

Count text that starts, ends, or contains a phrase

COUNTIF supports two wildcards in text criteria. An asterisk represents any number of characters, including none. A question mark represents exactly one character. These let you count partial matches without adding a separate column to the data.

  • Starts with a phrase: append an asterisk, as in "North*".
  • Ends with a phrase: put an asterisk before it, as in "*Ltd".
  • Contains a phrase anywhere: put an asterisk on both sides, as in "*North*".
  • Matches a variable character: use a question mark, such as "A?e" for a three-character value beginning with A and ending with e.

Suppose A2:A7 contains North, Northwind, South, North Star, East, and a blank. To count cells containing North anywhere in the text, use:

With those entries in A2:A7, enter =COUNTIF(A2:A7,"*North*"). It returns 3: North, Northwind, and North Star. COUNTIF is case-insensitive, so the capitalization of the phrase in the criterion does not limit the result.

For a starts-with test, use =COUNTIF(A2:A7,"North*") with the same layout. The result is also 3 for this example because all three matching entries begin with North. In other data, a contains test and a starts-with test will usually give different counts.

Count cells containing text only

If the goal is to count cells containing text, whatever the text says, use the wildcard criterion "*". With values in A2:A10, =COUNTIF(A2:A10,"*") counts text entries rather than requiring a particular word. It does not count ordinary numeric values as text matches. This is helpful for a quick count of labels in a mixed range.

Be clear about what “text only” means in your workbook. A number stored as text may be treated differently from a true numeric value, and a formula that displays an empty string is not the same as a genuinely empty cell in every counting scenario. If the range contains formulas that return "", test the result against a small sample before relying on the count. For a count of all nonempty cells regardless of whether they contain text or numbers, COUNTIF with "*" is not the right test; choose a method suited to the data and the blank-cell behavior you need.

Combine text with a condition

COUNTIF can also compare text against a value in another cell or combine a text criterion with an operator. To build a criterion from a cell reference, join the operator to that reference with an ampersand. For example, to count entries that are not equal to the text in D2, use:

With names in A2:A20 and the excluded name in D2, enter =COUNTIF(A2:A20,"<>"&D2). If D2 contains Alex, the formula counts cells that do not equal Alex, without regard to capitalization.

Use quotation marks around the operator and join it to the cell reference; do not put the cell reference inside the quoted text. For a text match that is literal, a simple reference such as =COUNTIF(A2:A20,D2) is clearer. If the criterion itself includes wildcard characters, Excel interprets them as wildcards unless you escape them.

Use COUNTIFS for multiple conditions

COUNTIF handles one condition. COUNTIFS applies multiple conditions, and a cell must meet all the supplied conditions to be counted. This is useful when the text match depends on another column, such as counting orders for one product in one region.

With product names in A2:A100 and regions in B2:B100, enter =COUNTIFS(A2:A100,"Tea",B2:B100,"West"). The result is the number of rows where the product is Tea and the region is West. The criteria ranges should cover the same rows so that each product is evaluated alongside the region in its row. COUNTIFS also supports wildcard criteria, so a condition such as "Tea*" can match product labels beginning with Tea.

Count text with exact capitalization

COUNTIF does not distinguish uppercase from lowercase. If capitalization matters—for example, if ABC and Abc are different codes—use EXACT to compare text case-sensitively, combined with SUMPRODUCT to count the TRUE results.

With codes in A2:A8, enter =SUMPRODUCT(--EXACT(A2:A8,"ABC")). If exactly two cells contain uppercase ABC and other entries include Abc, the result is 2. EXACT returns TRUE only when the text and capitalization match; the double unary operator converts TRUE and FALSE to 1 and 0 so SUMPRODUCT can add them. For ordinary text lists, COUNTIF is simpler and easier to maintain.

Escape wildcard characters and fix common problems

A common surprise is that an asterisk or question mark in a criterion normally acts as a wildcard, not as a literal character. To search for a literal wildcard, precede it with a tilde (~). For example, with product codes in A2:A50, enter =COUNTIF(A2:A50,"~*") to count cells whose value is a literal asterisk. Use "~?" to search for a literal question mark, and "~~" to search for a literal tilde.

If an apparently correct formula returns zero, check these common causes:

  • Extra spaces: "North" and "North " are not the same exact text. A contains criterion may still match both, but an exact criterion can miss a value with trailing spaces.
  • Wrong range: confirm that the formula points to the cells that actually contain the data, especially after copying a formula to another location.
  • Unintended wildcards: a * or ? in the criterion may be interpreted as a pattern. Escape it with ~ if it should be literal.
  • Numbers stored as text: a value that looks numeric may be stored as text. Check the source data and the type of match you intend to count.
  • Argument separators: this article uses commas, as in standard Microsoft 365 Excel for Windows syntax. Excel installations with different regional settings may display or require semicolons as argument separators.

To spot hidden spaces, inspect a suspicious cell in the formula bar or compare its length with a clean example. You can clean imported text in a helper column with functions such as TRIM, then count the cleaned values. Be aware that TRIM removes ordinary leading and trailing spaces and reduces repeated internal spaces; it does not remove every possible nonbreaking space copied from websites.

Frequently Asked Questions

Does COUNTIF distinguish uppercase and lowercase?

No. COUNTIF treats uppercase and lowercase text as equivalent. Use a case-sensitive approach such as SUMPRODUCT with EXACT when capitalization must match precisely.

How do I count cells that contain a word anywhere?

Put an asterisk before and after the word in the criterion. For example, with descriptions in A2:A100, =COUNTIF(A2:A100,"*late*") counts cells containing late anywhere, including a longer word that includes those letters.

How do I count blank cells with COUNTIF?

Use an empty-string criterion: with entries in A2:A100, =COUNTIF(A2:A100,"") counts cells Excel treats as blank for this criterion. If formulas return empty strings in the range, verify how those cells are counted in the specific workbook.

Can COUNTIF count two different text values?

One COUNTIF formula accepts one criterion. To add the counts for two separate text values, use two COUNTIF formulas joined with plus. With names in A2:A100, =COUNTIF(A2:A100,"Alex")+COUNTIF(A2:A100,"Jordan") adds the number of Alex entries and Jordan entries. Use COUNTIFS instead when one row must meet multiple conditions together.

Why does COUNTIF count a longer word than I expected?

Check whether the criterion includes an asterisk or question mark, since those are wildcards, and whether you intended an exact match or a contains match. A criterion like "*cat*" counts text such as category as well as cat. Use "cat" for an exact text match.

Choose the right text-counting formula

For a straightforward tally, start with an exact COUNTIF criterion. Add wildcards only when partial matches are intended, and use a cell reference for criteria that may change. Move to COUNTIFS when each counted row must satisfy more than one condition, or use a case-sensitive method only when capitalization carries meaning. Finally, test the formula on a few known entries and blanks; that quick check helps catch unwanted spaces, wildcard behavior, and differences between empty cells and formulas that display nothing.

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.