Yes, use COUNTIF(range, "*"&cell&"*") to count cells that contain the text from another cell. For example, =COUNTIF(A2:A10, "*"&B1&"*") counts how many cells in A2:A10 contain the text in B1 anywhere within them.
How COUNTIF Handles Text Matching
Excel’s COUNTIF function counts cells that meet a single condition. When the condition is text, it matches exact text by default. To match text that appears anywhere inside a cell, you wrap the search text with asterisks (*), which are wildcards that represent any number of characters. The formula "*"&B1&"*" tells Excel to find cells that start with anything, contain the value of B1, and end with anything.
This works for partial matches, so a cell containing “apple pie” will be counted if the reference cell contains “apple”. The match is case-insensitive, so “Apple” and “apple” are treated the same.
Practical Example with a Worked Formula
Suppose you have a list of product names in A2:A10 and you want to count how many contain the word “pro” typed in cell B1.
- In B1, enter:
pro - In C1, enter:
=COUNTIF(A2:A10, "*"&B1&"*")
If A2:A10 contains “ProDesk”, “Laptop Pro”, “ProBook”, “Tablet”, and “ProMax”, the formula returns 4 because four cells contain “pro” (case-insensitive). The asterisks are essential; without them, =COUNTIF(A2:A10, B1) would only count exact matches like “pro” alone.
Using Cell References in Criteria
You can reference any cell in the criteria string. The ampersand (&) concatenates the asterisks with the cell value. For example, if the search text is in D5, the formula is =COUNTIF(A2:A10, "*"&D5&"*"). This works in Excel 2010 and later, including Excel for Microsoft 365, Excel 2021, and Excel for the web. On Mac, the same formula works, but you may need to use semicolons as argument separators if your regional settings use them; the standard comma works in most English versions.
Common Problems and Solutions
- No matches returned: Check that the reference cell has no extra spaces. Use
=COUNTIF(A2:A10, "*"&TRIM(B1)&"*")to remove leading/trailing spaces. - Counting cells that contain a number: If B1 contains a number, the asterisks still work because Excel converts the number to text for the criteria. For instance,
=COUNTIF(A2:A10, "*"&123&"*")counts cells containing “123” as text. - Wildcard characters in the reference: If B1 contains an asterisk or question mark, Excel treats them as wildcards, not literal characters. To count a literal asterisk, use a tilde (
~) before it, like"*"&SUBSTITUTE(B1,"*","~*")&"*". - Case sensitivity: COUNTIF is not case-sensitive. If you need case-sensitive counting, use SUMPRODUCT with ISNUMBER and FIND, but that is more complex.
Alternatives: COUNTIFS, SUMPRODUCT, and Dynamic Arrays
For multiple conditions, use COUNTIFS with the same wildcard technique. For example, to count cells in A2:A10 that contain B1 and are also marked “Yes” in C2:C10, use =COUNTIFS(A2:A10, "*"&B1&"*", C2:C10, "Yes").
If you need to count based on a range of search terms, SUMPRODUCT can sum counts: =SUMPRODUCT(--ISNUMBER(SEARCH(B1, A2:A10))). This returns the same count but is case-insensitive and can be extended to multiple terms. In Excel 365, you can also use =SUM(--(ISNUMBER(SEARCH(B1, A2:A10)))) as a dynamic array formula that spills automatically.
Counting Cells That Contain Any of Several Text Strings
To count cells that contain text from any cell in a list, you can combine COUNTIF with SUMPRODUCT. Suppose search terms are in D2:D4. Use =SUMPRODUCT(--(ISNUMBER(SEARCH(D2:D4, A2:A10)))) but note that this counts each occurrence, not unique cells. For unique cells, use =SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(D2:D4), A2:A10)), ROW(INDIRECT("1:"&ROWS(D2:D4)))^0)>0)). This is advanced and requires Ctrl+Shift+Enter in older versions.
Frequently Asked Questions
How do I count cells that contain a specific word, not just part of a word?
Use a formula with spaces around the word: =COUNTIF(A2:A10, "* "&B1&" *") plus handle the first and last words separately. A simpler approach is =SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" ", " "&A2:A10&" ")))).
Can COUNTIF reference a cell with a formula result?
Yes, the cell can contain any formula that returns text. The reference cell’s value is used as-is, so =COUNTIF(A2:A10, "*"&B1&"*") works even if B1 contains =CONCATENATE("pro","duct").
Why does my COUNTIF return 0 when the text is clearly there?
Check for non-printing characters like line breaks or extra spaces. Use =COUNTIF(A2:A10, "*"&TRIM(CLEAN(B1))&"*") to clean the reference. Also ensure the range includes the correct cells and that the text isn’t a number formatted as text.
Is there a way to count cells that do NOT contain a certain text?
Use the not-equal operator with wildcards: =COUNTIF(A2:A10, "<>*"&B1&"*"). This counts cells that do not contain the text in B1 anywhere.
Does COUNTIF work with text from another sheet?
Yes, reference the other sheet normally, like =COUNTIF(A2:A10, "*"&Sheet2!B1&"*"). The sheet name with spaces needs single quotes: ='My Sheet'!B1.
Related
- Excel Before Close Event: How to Run Code on Save
- SUBSTRING in Snowflake: Syntax, Examples, and Tips
- Excel Audit Process: Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Budget Amount RemainingIf your total budget is in B2 and the amount spent is in C2, enter =B2-C2 in another…
- Excel Loan Payment Formula: PMT & How to Use ItThe quickest way to calculate a loan payment in Excel is the PMT function. In its simplest form,…
- Find Matching Names in Two Excel Columns With FormulasTo find names in column A that also appear in column B, enter =IF(COUNTIF($B$2:$B$100,A2)>0,"Match","Not found") beside the first…
- Excel COUNTIF Duplicate Values Only OnceTo count duplicate values only once in Excel, you need to count each distinct value that appears more…
- Excel Formula for Quarterly Payments: How to CalculateThe simplest Excel formula for a quarterly payment is =PMT(rate, nper, pv), where rate is the quarterly interest…
- COUNTIF Excel String: Exact Text and WildcardsWith the text to search in A2:A8 and the target string Apple, use =COUNTIF(A2:A8,"Apple"); in the example list…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.