Excel COUNTIF for Text That Contains a Word

Formulas & functions · Updated November 20, 2026

To count cells that contain a particular word or phrase anywhere in their text, use Excel’s COUNTIF function with an asterisk on both sides of the search text. With the text to search in A2:A100, the pattern is =COUNTIF(A2:A100,"*word*"); replace word with the text you need to find.

The basic COUNTIF formula

COUNTIF checks a range against one criterion and returns the number of cells that meet it. Its arguments are the range to inspect and the criterion to apply. For a partial text match, the asterisks act as wildcards: the first allows any characters before the search text, and the second allows any characters after it. This lets Excel find the text at the beginning, middle, or end of a cell.

For a concrete example, suppose A2:A8 contains North warehouse, south warehouse, North office, Returns, warehouse audit, North Warehouse, and one blank cell. With those values in A2:A8, use:

=COUNTIF(A2:A8,"*warehouse*")

The result is 4: four cells contain warehouse somewhere in their text. COUNTIF counts matching cells, not the number of times the word appears inside a cell. If one cell says warehouse warehouse, that cell still contributes only 1 to this result.

How the asterisk wildcard works

In Excel criteria, an asterisk (*) represents any sequence of zero or more characters. Putting it before and after a word creates a contains search. The same wildcard can be used in other positions: warehouse* finds text that starts with warehouse, while *warehouse finds text that ends with it. Those are partial matches too, but they are not the same as searching anywhere in the cell.

The question mark (?) is another wildcard. It represents one character, rather than any-length text. For example, a criterion shaped like AB?12 matches a five-character sequence with any single character in the third position. Use question mark when the variable part has a known length; use asterisk when its length can vary.

  • *oak* can match oak, oak tree, or blackoaks.
  • oak* matches text beginning with oak, such as oak tree, but not black oak.
  • *oak matches text ending with oak, such as black oak.
  • o?k matches a three-character sequence such as oak or o:k.

COUNTIF text matching is not case-sensitive. Searching for warehouse also matches Warehouse and WAREHOUSE. A contains criterion searches for a sequence of characters, not a whole word, so searching for art can match artist or cart as well as art. That behavior is useful for fragments, but it matters when similar words should not be included.

Enter a search term in a cell

Hard-coding the search word in a formula is convenient for a one-off count. If other people will change the search term, putting it in a worksheet cell makes the result easier to update without editing the formula. For example, place warehouse in D2 and keep the descriptions in A2:A8. With that layout, enter:

=COUNTIF(A2:A8,"*"&D2&"*")

The result is 4 for the sample data above. The ampersands join the wildcard characters to the value in D2, creating the criterion that COUNTIF needs. If D2 changes to office, the count updates to the number of cells containing office. This approach is also useful when a search term comes from a dropdown list or another formula.

When a referenced search cell might be empty, decide what an empty search should mean before relying on the result. An empty text string placed between two asterisks effectively creates a pattern that can match any text cell, which may produce a surprisingly large count. You can keep the input cell visibly labeled, require a search term, or use a separate check around the counting formula if blank input should return a message instead.

Count text that contains more than one condition

COUNTIF applies one criterion. If a row must meet two conditions at once—for example, a description contains a word and the corresponding status is Open—use COUNTIFS. It applies multiple criteria as an AND test, so each counted row must satisfy every condition. The ranges used for the conditions should have matching dimensions.

Suppose descriptions are in A2:A8 and statuses are in B2:B8. To count rows whose description contains warehouse and whose status is Open, use:

=COUNTIFS(A2:A8,"*warehouse*",B2:B8,"Open")

For an OR search—cells containing either oak or pine—you can add two COUNTIF results. With text in A2:A100, the formula is:

=COUNTIF(A2:A100,"*oak*")+COUNTIF(A2:A100,"*pine*")

This addition counts cells meeting either search, but a cell containing both words is counted twice. If each matching cell should count only once even when it contains both terms, use a method designed to de-duplicate the matches or add a helper column; simply adding the two counts is not an exclusive one-count-per-cell total.

Search for a literal asterisk or question mark

Because * and ? have special meanings in criteria, searching for those characters themselves requires an escape character. In Excel criteria, a tilde (~) makes the next wildcard character literal. For example, with product descriptions in A2:A100, this counts cells containing an actual asterisk:

=COUNTIF(A2:A100,"*~**")

The first and last asterisks mean any text before and after the target; the tilde makes the middle asterisk a literal character. To search for an actual question mark instead, use:

=COUNTIF(A2:A100,"*~?*")

A literal tilde also needs escaping. In criteria, two tildes represent one actual tilde. This escaping detail is a common reason a search for symbols returns an unexpected count: without the tilde, Excel treats the symbol as a pattern instruction instead of ordinary text.

When COUNTIF is not the right match

COUNTIF is a good fit when you want the number of cells containing a text fragment and case does not matter. It is not a word-boundary search, a case-sensitive search, or a way to count each occurrence inside a cell. For case-sensitive partial matching, Microsoft 365 for Windows can use FIND inside SUMPRODUCT. If target text is in B2:B6, and the goal is to count cells containing uppercase ABC with matching case, use:

=SUMPRODUCT(--ISNUMBER(FIND("ABC",B2:B6)))

For values ABC-104, abc-105, Batch ABC, ABCD, and a blank cell in B2:B6, this returns 3. FIND is case-sensitive, and ISNUMBER identifies whether it found the text; SUMPRODUCT totals the resulting matches. This still finds ABC within a longer string, so it does not enforce whole-word boundaries.

If the goal is to count occurrences rather than cells, COUNTIF is also the wrong tool: a cell with the same word repeated is counted once. Counting occurrences requires a different formula that compares text lengths before and after removing the target, with care around case and overlapping matches. For most category, inventory, and survey checks, however, a per-cell count is the intended result.

Common problems and quick checks

  • The result is zero: Check that the search term is spelled as it appears in the cells, that the range covers the intended rows, and that the criterion has a wildcard on both sides for a contains search. Extra spaces in the source text can also make a phrase differ from what it appears to be.
  • The result is too high: Remember that matching is case-insensitive and can find a fragment inside a longer word. A search for cat can match catalog. Narrow the range or use a more distinctive phrase if those are unwanted matches.
  • The formula displays an error or does not calculate: Check for a missing quote, parenthesis, or ampersand. Excel uses commas between arguments in the examples here; some regional settings use semicolons as argument separators, so follow the separator style Excel accepts on that computer.
  • The count changes after adding records: A fixed range such as A2:A100 does not automatically include rows below row 100. Expand the range or use an Excel Table so its structured data range grows as rows are added.
  • You are matching numeric-looking content: COUNTIF with wildcard text criteria is intended for text-pattern matching. If values are numbers, dates, or mixed types, check how the data is stored and choose a criterion that matches the actual cell values rather than relying on how they look when formatted.

In Microsoft 365 for Windows, type the formula in an empty cell and press Enter. Excel recalculates it when the referenced cells change, provided calculation is set to Automatic. Excel for Mac supports the same COUNTIF function and wildcard criteria; ribbon layout and keyboard shortcuts can differ, but the formula itself does not. In Excel for the web, the same formula syntax works in a worksheet cell.

Frequently Asked Questions

Can COUNTIF find text anywhere in a cell?
Yes. Place an asterisk before and after the search text in the criterion. That pattern can match characters on either side of the text, so the search term can occur at the start, middle, or end of a cell.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF text comparisons are case-insensitive, so uppercase and lowercase versions match. Use a case-sensitive approach such as FIND with SUMPRODUCT when capitalization must matter.

How do I count cells containing a word listed in another cell?
Join wildcard asterisks to the reference cell with ampersands, as in the D2 example above. This lets you change the search text in the worksheet rather than editing the formula.

Can COUNTIF count two different words?
For cells containing either of two terms, add two COUNTIF results. A cell containing both terms will be counted twice by that method. For two conditions that must both be true on a row, use COUNTIFS instead.

Does COUNTIF count repeated words in one cell more than once?
No. COUNTIF returns one match for a cell that meets its criterion, regardless of how many times the term appears within that cell. Counting occurrences requires a separate approach.

Choosing the simplest reliable approach

For an ordinary contains search, start with COUNTIF and the two asterisks. Keep the range limited to the data you need, use a cell reference for a changeable search term, and check whether partial-word matches are acceptable. Move to COUNTIFS when several conditions must all be true, use separate counts for a simple OR test while considering overlapping matches, and choose a case-sensitive or occurrence-counting method only when that distinction is important. These choices make the result easier to check and help prevent a plausible-looking count from answering the wrong question.

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.