COUNTIF Excel Contains Part of Text: Examples & Tips

Formulas & functions · Updated November 22, 2026

Yes. In Excel, use COUNTIF with asterisks around the text you want to find: =COUNTIF(A2:A8,"*North*") counts cells in A2:A8 that contain “North” anywhere in their text. The asterisks are wildcards, so the matching cells can have other characters before or after the searched text.

How COUNTIF finds part of a text string

COUNTIF checks a range and counts the cells that meet one criterion. Its syntax is =COUNTIF(range, criteria). The range is the group of cells to inspect; the criterion tells Excel what qualifies as a match. For a partial text match, put the text between a leading and trailing asterisk inside quotation marks.

For example, suppose A2:A8 contains these entries: Northwind, Northwind Traders, Southwind, Northwind, Northwind Retail, North Wind, and northwind. With those values in A2:A8, the formula =COUNTIF(A2:A8,"*North*") returns 5. It counts the entries containing the consecutive letters North, including “Northwind Traders” and “Northwind Retail.” It also counts “northwind” because COUNTIF does not distinguish uppercase from lowercase. “North Wind” does not match: the space interrupts the sequence of letters.

COUNTIF counts matching cells, not the number of times the searched text appears. If one cell contains “North North,” it still contributes one to the result. That difference matters when you are counting records, products, or comments rather than occurrences within each cell.

What the asterisk and question-mark wildcards mean

Excel uses two wildcard characters in text criteria. An asterisk (*) matches any sequence of characters, including no characters. A question mark (?) matches exactly one character. You can combine them to describe a pattern rather than one fixed string.

  • "*North*" matches North at the beginning, middle, or end of a cell.
  • "North*" matches text that begins with North, such as Northwind or North Region, but not South North.
  • "*North" matches text that ends with North, such as South North, but not Northwind.
  • "A?C" matches a three-character value whose first and last characters are A and C, such as ABC or A7C.

The placement of the wildcards changes the question you are asking. Use asterisks on both sides when the phrase could appear anywhere. Use just one when you specifically want a beginning or ending match. A wildcard can also match an empty sequence, which is why the two-sided pattern is a convenient way to find a substring in longer text.

Use a cell reference for the text to search

If the search word changes regularly, keep it in a separate cell instead of editing the formula each time. Assume the text values are in A2:A8 and H2 contains the search term wind. The formula =COUNTIF(A2:A8,"*"&H2&"*") returns 6 for the example list above. Excel joins the wildcard characters to the value in H2, creating a criterion equivalent to "*wind*".

This setup is useful for a small search box, a report control, or a list of terms you want to check one at a time. If H2 is empty, the joined criterion is effectively two asterisks, so it can match every text entry. If an empty search should return zero instead, add a check around COUNTIF. With the data in A2:A8 and the search term in H2, =IF(H2="",0,COUNTIF(A2:A8,"*"&H2&"*")) returns 0 when H2 is blank and otherwise counts the matches.

In the formulas shown here, commas separate arguments, as in a standard English-language Excel installation. Some regional settings use semicolons as the argument separator. If Excel rejects a formula with commas, use the separator configured for your installation.

Combine partial text with another condition

COUNTIF handles one criterion. When you need to require both a partial text match and another condition, use COUNTIFS. For example, suppose A2:A7 contains Northwind, Northwind Traders, Southwind, Northwind, Northwind Retail, and North Wind. Suppose the corresponding cells B2:B7 contain Open, Closed, Open, Open, Open, and Open. With product names in A2:A7 and statuses in B2:B7, =COUNTIFS(A2:A7,"*North*",B2:B7,"Open") returns 3: the first, fourth, and fifth records contain North and have an Open status.

COUNTIFS applies each criterion to its corresponding range. In this example, the first range checks names and the second checks statuses. The ranges should have the same dimensions so each row’s name is evaluated alongside that row’s status. You can add further range-and-criterion pairs when you need additional conditions, such as a region or date range.

For a status condition, you can use a cell reference too. If H2 contains North and H3 contains Open, the formula for names in A2:A7 and statuses in B2:B7 is =COUNTIFS(A2:A7,"*"&H2&"*",B2:B7,H3). It returns 3 with the sample data above. This keeps the criteria easy to change without rewriting the formula.

Case sensitivity and literal wildcard characters

COUNTIF is not case-sensitive. A search for North also matches north, NORTH, and other capitalization variants. For ordinary lists, that is usually convenient. If capitalization must matter, COUNTIF alone is not the right tool. With the sample values in A2:A8, =SUMPRODUCT(--ISNUMBER(FIND("North",A2:A8))) returns 5. FIND is case-sensitive, and SUMPRODUCT counts the cells where FIND locates the exact-case text. In this example, “northwind” is not included.

There is another detail to watch: an asterisk or question mark in the search term is normally treated as a wildcard, not as a literal character. A tilde (~) tells Excel to treat the next wildcard character literally. For instance, if A2:A8 contains labels and you want to count cells containing an actual asterisk, use =COUNTIF(A2:A8,"*~**"). The first and last asterisks mean “any text before or after”; the middle tilde-asterisk means “a literal asterisk.” Use ~? for a literal question mark and ~~ for a literal tilde.

Common problems and how to fix them

A partial-match formula can return an unexpected count even when its syntax is valid. Check the criterion, the data, and the range before changing functions. These are frequent causes:

  • The wildcards are missing. A criterion such as "North" looks for a cell matching that text, rather than text with North embedded in a longer value. Use "*North*" for a contains match.
  • The search text is not consecutive. The pattern “North” does not match “N orth” or “North Wind” if the intended substring includes a space. Search for the actual sequence, such as "*North Wind*", if that is what the data contains.
  • Extra spaces or inconsistent source data are present. Leading, trailing, or repeated spaces can affect exact text criteria and make the contents look different from what they are. Inspect a few source cells or clean the data before relying on a count.
  • The range excludes some records. If new rows are added below A8, a formula that still checks A2:A8 will not include them. Expand the range or use an Excel Table so formulas can refer to a data column that grows with the table.
  • The formula treats punctuation as a wildcard. If the text you are searching for includes * or ?, escape that character with a tilde as described above.
  • The result is zero because the cell reference is blank or wrong. Check that the searched term is in the cell referenced by the formula and that the formula points to the intended data range.

When checking a formula, select it in the formula bar and verify that the first and last asterisks are inside the quoted criterion. In a cell-reference version, the ampersands join the quoted wildcard text to the reference. A missing ampersand or misplaced quotation mark can change the criterion or cause a formula error.

Choosing between COUNTIF and other approaches

Choose the function based on the kind of count you need. COUNTIF is the straightforward option for one range and one condition, including a partial text condition. COUNTIFS is a better fit when every counted row must meet multiple conditions. Both functions are easy to fill down or reuse in summaries.

If you need a case-sensitive count, FIND combined with SUMPRODUCT is one alternative. If you need to count how many times a phrase occurs inside each cell, a simple COUNTIF is not enough because it counts qualifying cells only. If you need a flexible, multi-step analysis, functions such as FILTER can return matching rows rather than just count them; FILTER is available in Microsoft 365 and Excel 2021 or later. Pick the simplest method that answers the actual question, and verify it against a few records you can inspect manually.

These formulas work in Excel for Microsoft 365 on Windows. COUNTIF and COUNTIFS are also available in Excel for Mac and Excel for the web; the basic syntax is the same. Menu differences are not relevant because you enter the formula directly in a cell. Keep in mind that a count is only as reliable as the text being searched: inconsistent spelling, abbreviations, and different punctuation may represent the same thing to a person but not the same substring to Excel.

Frequently Asked Questions

Does COUNTIF match text in the middle of a cell?
Yes. Put an asterisk before and after the search text in the criterion, as in "*word*". The pattern matches the word anywhere in the cell.

Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase letters as equivalent. For a case-sensitive search, consider FIND with SUMPRODUCT instead.

Can COUNTIF search for part of the text stored in another cell?
Yes. Join the wildcard characters to the cell reference, for example =COUNTIF(A2:A20,"*"&H2&"*"). This searches A2:A20 for the text currently in H2.

Why does my COUNTIF formula return zero?
Check that the criterion has wildcards on both sides, that the search text is spelled as it appears in the data, and that the selected range includes the relevant cells. Also check for spaces or punctuation that interrupt the searched sequence.

Can COUNTIF count repeated appearances in one cell?
No. It counts matching cells, so a cell contributes at most one to the result. Counting repeated occurrences inside each cell requires a different formula or approach.

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.