COUNTIF Cell Contains Text in Excel: Formula and Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

To count cells that contain a specific piece of text, use COUNTIF with wildcard asterisks around the search term. The formula =COUNTIF(A2:A11,"pro") counts every cell in the range whose text includes the substring "pro" anywhere inside it. This article shows the exact syntax, a worked example, and the mistakes that cause wrong counts when you use COUNTIF cell contains text logic.

Quick Answer

• Wrap the search text in asterisks: "text" matches any cell that contains "text" anywhere.
• =COUNTIF(range,"pro") counts cells containing "pro" as a substring.
• Wildcards work on text values. They do not match numbers or dates the way you might expect.
• COUNTIF is not case sensitive. "pro" and "PRO" return the same count.
• For a case sensitive check, use SEARCH or FIND inside SUMPRODUCT instead of COUNTIF.

Syntax

The COUNTIF function takes two arguments.

The wildcard characters are:

So "pro" means "any characters, then pro, then any characters." A cell must contain the letters p, r, o in that order to match. The asterisks allow other text before and after.

For a full breakdown of the function, see the COUNTIF function guide.

How It Works

COUNTIF scans each cell in the range and tests it against the criteria. When the criteria is a text string with wildcards, Excel treats it as a pattern match instead of an exact match.

The pattern "pro" breaks down like this:

• The leading * allows any characters before "pro".
• The literal text pro must appear in that order.
• The trailing * allows any characters after "pro".

A cell holding "ProBook Laptop" matches because "Pro" appears at the start. A cell holding "Wireless Mouse" does not match because the letters p, r, o never appear together in that order. A cell holding "ProGrip Keyboard" matches.

Two behaviors matter here:

1. Case is ignored. COUNTIF does not distinguish uppercase from lowercase. "Pro", "PRO", and "pro" all match the same pattern.
2. The match is a substring, not a word. "pro" matches "product", "approve", and "improve" because "pro" appears inside each word. If you want whole words only, the wildcard approach will overcount.

The formula returns a single number: the count of cells that matched.

Worked Example

The table below lists product names in column A, categories in column B, and formulas in column C. Cell C2 counts product names containing "pro". Cells C3 to C11 check each row individually with SEARCH.

Column C shows the COUNTIF result in C2 and a per-row SEARCH check in C3:C11 for product names containing "pro".

The COUNTIF formula in C2 returns 5. The per-row checks in C3:C11 return "Yes" for four rows (ProGrip Keyboard, ProSound Speaker, ProCam Webcam, ProLight Desk Lamp). The fifth match is ProBook Laptop in A2, which shares row 2 with the COUNTIF formula and so has no per-row check of its own.

The per-row SEARCH column is a useful cross-check. SEARCH returns the position of "pro" inside the text, and ISNUMBER converts that to TRUE or FALSE, which IF turns into "Yes" or "No".

If you need a count that matches the SEARCH logic, wrap it in SUMPRODUCT:

=SUMPRODUCT(--ISNUMBER(SEARCH("pro",A2:A11)))

This counts the rows where SEARCH finds the substring and returns 5 here, the same as the COUNTIF formula. SEARCH is not case sensitive, so swap in FIND if you need a case sensitive count. For more on the text check pattern, see Excel IF cell contains text and Excel CONTAINS.

More Examples

Count cells that start with a term. Use a trailing wildcard only.

=COUNTIF(A2:A11,"Pro*")

This counts cells whose text begins with "Pro".

Count cells that end with a term. Use a leading wildcard only.

=COUNTIF(A2:A11,"*Laptop")

Count cells containing one of several terms. Add two COUNTIF results.

=COUNTIF(A2:A11,"*pro*")+COUNTIF(A2:A11,"*cam*")

This can double count a cell that contains both terms. To avoid that, use SUMPRODUCT with ISNUMBER and SEARCH across multiple terms.

Count cells containing a literal asterisk. Escape it with a tilde.

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

Count non blank cells. A different pattern applies. See COUNTIF not blank.

Count all cells with any text. Use "*" as the criteria.

=COUNTIF(A2:A11,"*")

This counts cells that contain at least one character of text. For a fuller treatment, see how to count cells with text.

Errors and How to Fix Them

The formula returns 0 when you expect a positive count. Check that the asterisks are inside the quotes. "pro" is a wildcard pattern. pro alone is an exact match and will only count cells equal to "pro". Also confirm the range covers the cells you mean.

The count is higher than expected. The substring matches inside longer words. "pro" counts "approve" and "improve". If you need whole words, use a different method such as SUMPRODUCT with ISNUMBER and SEARCH on padded text, or filter the data first.

The formula returns #VALUE!. COUNTIF returns #VALUE! when the criteria string is longer than 255 characters or when the range points to a closed workbook. Shorten the criteria or open the source workbook.

The count does not change when you edit the data. Calculation may be set to manual. Check the calculation mode in the Formulas area of the ribbon.

Numbers are not counted. COUNTIF with a text wildcard pattern matches text values. A cell holding the number 12345 will not match "123" as text unless it is stored as text. Convert the values to text if you need to match them.

Common Mistakes

• Forgetting the asterisks. =COUNTIF(A2:A11,"pro") counts only cells exactly equal to "pro". Fix: use "pro" for a substring match.
• Expecting case sensitivity. COUNTIF ignores case, so "Pro" and "pro" give the same result. Fix: use SUMPRODUCT with FIND if case matters.
• Matching inside longer words by accident. "pro" matches "approve". Fix: use word boundaries or a helper column with SEARCH.
• Using wildcards on numeric ranges. Wildcards match text, not numbers. Fix: convert numbers to text or use numeric criteria.
• Leaving out the range lock. When you copy the formula down, a relative range shifts. Fix: use $A$2:$A$11 if the range must stay fixed.
• Assuming COUNTIF and SEARCH agree. They use different matching rules. Fix: pick one method and verify with a helper column, as in the worked example.

Limitations

COUNTIF with wildcards cannot do case sensitive matching. It also cannot match on word boundaries, so a short substring will match inside longer words and inflate the count. The wildcard pattern is a simple pattern, not a regular expression. You cannot express alternation, optional groups, or anchored word matches with * and ? alone.

The function also treats text and numbers differently. A wildcard pattern will not reliably match numeric values, and it will not match dates as text. If your data mixes types, the count can be misleading. For anything beyond a simple substring, a helper column with SEARCH or FIND gives you more control and a visible audit trail.

Frequently Asked Questions

How do I count cells that contain specific text in Excel?

Use =COUNTIF(range,"text"). The asterisks on both sides tell Excel to match any cell that contains "text" anywhere inside it. Replace range with your cell range and text with the substring you want.

Is COUNTIF case sensitive?

No. COUNTIF ignores case, so "pro" and "PRO" return the same count. If you need a case sensitive count, use SUMPRODUCT with FIND, which distinguishes uppercase from lowercase.

Why does my COUNTIF return 0 when the text is clearly there?

The most common cause is missing asterisks. "pro" matches only cells equal to "pro". Use "pro" for a substring match. Also check that the range covers the correct cells and that the values are stored as text, not numbers.

How do I count cells that contain one of several words?

Add separate COUNTIF formulas, one per word. Be aware that a cell containing two of the words will be counted twice. To count each matching cell once, use SUMPRODUCT with ISNUMBER and SEARCH across the terms.

Can COUNTIF match a wildcard character literally?

Yes. Put a tilde before the wildcard. To match a literal asterisk, use "~*". To match a literal question mark, use "~?". The tilde tells Excel to treat the next character as text instead of a wildcard.

References

This article draws on the standard references listed under Further Reading.

Further Reading

• Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician
• Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology
• Microsoft Support: Excel help and learning
• McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis
• Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology
• Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology

Related Articles

• COUNTIF Function in Excel: Syntax and Examples
• COUNTIF Not Blank in Excel: Formula and Examples
• Excel IF Cell Contains Text: Formula Examples and Wildcards
• Excel CONTAINS: How to Check If a Cell Contains Text
• How to Count Cells With Text in Excel (Step by Step)