To count cells containing specific text in Excel, use the COUNTIF function with wildcards: =COUNTIF(range,"*text*"). For example, =COUNTIF(A2:A20,"*apple*") counts every cell in A2:A20 that contains the word “apple” anywhere in the text.
Why COUNTIF with Wildcards Works
Excel’s COUNTIF function counts cells that meet a single condition. The asterisk (*) is a wildcard that matches any sequence of characters. Placing * before and after your text tells Excel to count cells where that text appears at the start, middle, or end of the cell’s content. Without the asterisks, COUNTIF would only match cells that are exactly equal to the text (ignoring extra characters).
For instance, if a cell contains “green apple pie,” =COUNTIF(A2:A20,"*apple*") counts it, but =COUNTIF(A2:A20,"apple") does not because the cell isn’t exactly “apple”.
Exact Match vs. Partial Match: Which to Use
Decide whether you need an exact match or a partial match:
- Exact match (whole cell equals the text): Use
=COUNTIF(range,"text"). For example,=COUNTIF(B2:B10,"Paid")counts only cells that contain exactly “Paid” with no other characters. - Partial match (text appears anywhere): Use
=COUNTIF(range,"*text*"). This counts cells where the text is a substring, such as “Overdue” in “Invoice Overdue”. - Starts with or ends with: Use
"text*"for starts-with, and"*text"for ends-with. For example,=COUNTIF(C2:C20,"AC*")counts cells starting with “AC”.
Note that COUNTIF is case-insensitive, so “Apple” and “apple” are treated the same. If you need case-sensitive counting, use SUMPRODUCT with ISNUMBER and FIND (see below).
Counting with Multiple Criteria
When you need to count cells that meet several conditions, use COUNTIFS. For example, to count cells in A2:A20 that contain “apple” and also have a value greater than 10 in B2:B20:
=COUNTIFS(A2:A20,"*apple*",B2:B20,">10")
This returns the number of rows where both conditions are true. You can add more pairs of range and criteria as needed.
Case-Sensitive Counting
Because COUNTIF ignores case, use this array formula (or SUMPRODUCT) to count cells that contain a specific text string and respect case:
=SUMPRODUCT(--ISNUMBER(FIND("Apple",A2:A20)))
With text in A2:A20, this counts cells containing “Apple” exactly as typed (capital A). FIND is case-sensitive, and ISNUMBER converts the position result to TRUE/FALSE; the double negative (--) turns TRUE/FALSE into 1/0 so SUMPRODUCT can sum them. Entering this as a normal formula works in Excel 365 and older versions; no Ctrl+Shift+Enter is needed for SUMPRODUCT.
Counting Cells That Do NOT Contain Specific Text
To count cells that do not contain a certain text, use the not-equal-to operator with a wildcard: =COUNTIF(range,"<>*text*"). For example, =COUNTIF(A2:A20,"<>*apple*") counts cells that do not contain “apple”. This includes empty cells, so if you want to exclude blanks as well, use =COUNTIFS(A2:A20,"<>*apple*",A2:A20,"<>").
Common Problems and Solutions
- Counting cells with a literal asterisk or question mark: If your text includes an actual asterisk or question mark, you must escape it with a tilde (
~). For example, to count cells containing the literal string “*”, use=COUNTIF(A2:A20,"~*"). - Text that looks like a number: If your criteria is a number (e.g., “123”), COUNTIF treats it as text if you use quotes. To count cells that contain the number 123 as text, use
=COUNTIF(A2:A20,"*123*"). This works because Excel converts the number to text for comparison. - Leading/trailing spaces: If cells have extra spaces, the wildcard may not match as expected. Use
=COUNTIF(A2:A20,"*"&TRIM("text")&"*")or clean the data with the TRIM function first. - Counting across multiple sheets: COUNTIF works on a single range. To count across sheets, use a 3D reference like
=COUNTIF(Sheet1:Sheet3!A2:A20,"*apple*")in Excel 365, but note that this only works if the sheets are adjacent and the range is identical.
Alternatives to COUNTIF
For more complex counting, consider:
- SUMPRODUCT with ISNUMBER(SEARCH): Similar to the case-sensitive version but case-insensitive:
=SUMPRODUCT(--ISNUMBER(SEARCH("apple",A2:A20))). SEARCH is case-insensitive. - FILTER and COUNTA (Excel 365): Use
=COUNTA(FILTER(A2:A20,ISNUMBER(SEARCH("apple",A2:A20))))to count filtered results. This is useful if you also want to see the matching rows. - COUNTIF with cell reference: Instead of hardcoding text, use a cell reference:
=COUNTIF(A2:A20,"*"&D1&"*"). If D1 contains “apple”, this counts cells containing that value. This makes the formula dynamic.
Frequently Asked Questions
How do I count cells that contain specific text and ignore blank cells?
Use =COUNTIFS(range,"*text*",range,"<>"). The second condition "<>" excludes empty cells. For example, =COUNTIFS(A2:A20,"*apple*",A2:A20,"<>") counts non-blank cells containing “apple”.
Can I count cells that contain one of several text values?
Yes, use SUMPRODUCT with multiple COUNTIFs: =SUMPRODUCT(COUNTIF(range,{"*apple*","*banana*"})). This counts cells that contain either “apple” or “banana” (cells containing both are counted only once because each cell is checked against each criterion, and the sum adds the counts, but a cell with both would be counted twice; to avoid double-counting, use a more complex array formula). For a simpler approach, add two COUNTIFs: =COUNTIF(range,"*apple*")+COUNTIF(range,"*banana*") but this double-counts cells with both. To count unique cells, use =SUMPRODUCT(--((ISNUMBER(SEARCH("apple",range)))+(ISNUMBER(SEARCH("banana",range)))>0)).
Why does my COUNTIF return 0 when I know there are matches?
Check for invisible characters like non-breaking spaces or line breaks. Use the CLEAN and TRIM functions to remove them: =COUNTIF(A2:A20,"*"&TRIM(CLEAN(D1))&"*"). Also ensure the range is correct and doesn’t include merged cells, which can cause issues.
Does COUNTIF work with numbers that are stored as text?
Yes, if the cell contains a number stored as text (e.g., from a CSV import), COUNTIF with wildcards will match it. For example, =COUNTIF(A2:A20,"*123*") counts cells containing the text “123” even if they are numeric values formatted as text.
How do I count cells that contain specific text in a filtered list?
Use the SUBTOTAL function with function number 3 (COUNTA) or 2 (COUNT), but SUBTOTAL does not support wildcards directly. Instead, use a helper column with a formula like =IF(ISNUMBER(SEARCH("apple",A2)),1,0) and then use SUBTOTAL(9, helper_range) to sum visible cells. Alternatively, use the FILTER function in Excel 365: =ROWS(FILTER(A2:A20,ISNUMBER(SEARCH("apple",A2:A20)))).
Related
- Excel Conditional Formatting Duplicate Rows: Full Guide
- Excel Conditional Formatting Date Occurring: How To
- Excel Conditional Formatting Color Scales Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Schedule an Email in Outlook on Desktop and WebTo schedule an email in Outlook, write your message, open the arrow or menu beside Send, choose Schedule…
- Schedule an Email in Outlook Offline: What WorksYou can prepare a delayed email in classic Outlook while offline, but it cannot leave your computer until…
- Mail Merge Toolkit in Word: Install and Get StartedTo get Mail Merge Toolkit in Word, download its installer from the official MAPILab website, close Office apps,…
- How to Retrieve Archived Emails in Outlook on Any DeviceTo retrieve an archived email in Outlook, open the Archive folder, find the message, then move it back…
- Excel Not Responding? Troubleshoot Common Causes and FixesIf Excel says “Not Responding,” wait briefly first, especially if you opened a large workbook or started a…
- Microsoft Excel Text to Columns: Split Data ClearlyTo convert text to columns in Microsoft Excel for Microsoft 365 on Windows, select the cells, go to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.