To count cells that contain a word, rather than cells that equal it, put an asterisk on each side of the word: =COUNTIF(D2:D13, "*apple*"). The asterisk stands for “any characters, or none”. The details are where counts go wrong: case, whole words, words that overlap, and items that contain a real asterisk or question mark.
Column D of our test sheet holds twelve items:
Apple pie, Pineapple, apple juice, Banana, Cherry, Apple*, Grape, Mango, Applesauce, Kiwi?, Pear, Plum
Five of them contain “apple” in some form. Two include a literal * or ?, which COUNTIF would otherwise read as wildcards.
Tested in Google Sheets. Every formula on this page was run on November 12, 2026, and the results below are the ones Google Sheets returned. How we test
Contains, starts with, ends with
| Formula | Result | Matched |
|---|---|---|
=COUNTIF(D2:D13, "*apple*") |
5 | Apple pie, Pineapple, apple juice, Apple*, Applesauce |
=COUNTIF(D2:D13, "apple") |
0 | Nothing: without asterisks, the whole cell must be “apple” |
=COUNTIF(D2:D13, "apple*") |
4 | Starts with apple: all of the above except Pineapple |
=COUNTIF(D2:D13, "*apple") |
1 | Ends with apple: Pineapple |
=COUNTIF(D2:D13, "* *") |
2 | Contains a space: Apple pie, apple juice |
=COUNTIF(D2:D13, "P?ar") |
1 | ? is exactly one character: Pear |
=COUNTIF(D2:D13, "<>*apple*") |
7 | Does not contain apple |
COUNTIF ignores case: "*Apple*" also returned 5.
The word in a cell
Join the asterisks to the cell with &. With “apple” in L2:
=COUNTIF(D2:D13, "*"&L2&"*")
returned 5. Writing "*L2*" instead returned 0 with no error, because COUNTIF searched for the letters “L2”.
Contains text, plus another condition
COUNTIFS accepts wildcards in any of its conditions. Items containing “apple” whose status in column C is Paid:
=COUNTIFS(D2:D13, "*apple*", C2:C13, "Paid")
returned 1 (Apple pie). To add up instead of count, use SUMIF: =SUMIF(D2:D13, "*apple*", E2:E13) totaled the amounts of the five apple items, 470. More conditions are covered in COUNTIF with multiple criteria.
Contains one word or another
For two words that never appear in the same cell, two COUNTIFs added together are fine: apple or cherry returned 6. When one cell can contain both words, the sum counts it twice. “Pineapple” contains both “apple” and “pine”:
| Formula | Result |
|---|---|
=COUNTIF(D2:D13, "*apple*") + COUNTIF(D2:D13, "*pine*") |
6: Pineapple counted twice |
=SUMPRODUCT(--REGEXMATCH(D2:D13, "(?i)apple|pine")) |
5 |
REGEXMATCH checks each cell once, so a cell that contains both words still counts as one. | means “or”, and (?i) makes the match ignore case, since REGEXMATCH is case-sensitive by default.
Case-sensitive counts
COUNTIF can’t tell “Apple” from “apple”. Two formulas can:
| Formula | Result | Matched |
|---|---|---|
=SUMPRODUCT(--REGEXMATCH(D2:D13, "Apple")) |
3 | Apple pie, Apple*, Applesauce |
=SUMPRODUCT(--ISNUMBER(FIND("apple", D2:D13))) |
2 | Pineapple, apple juice |
=SUMPRODUCT(--ISNUMBER(SEARCH("apple", D2:D13))) |
5 | SEARCH ignores case, like COUNTIF |
Keep the SUMPRODUCT. =SUM(--REGEXMATCH(D2:D13, "Apple")) returned #VALUE! with a message asking for ARRAYFORMULA, because REGEXMATCH only checks the whole range inside an array function.
Whole words only
"*apple*" also matches Pineapple and Applesauce. To count “apple” only as a separate word, use a word boundary, \b:
=SUMPRODUCT(--REGEXMATCH(D2:D13, "(?i)\bapple\b"))
This returned 3: Apple pie, apple juice and Apple*. The asterisk after “Apple” isn’t a letter, so it ends the word.
Cells that contain a real * or ?
Put a tilde, ~, before a * or ? to look for the character itself:
| Formula | Result |
|---|---|
=COUNTIF(D2:D13, "*~**") (contains an asterisk) |
1: Apple* |
=COUNTIF(D2:D13, "*~?*") (contains a question mark) |
1: Kiwi? |
=COUNTIF(D2:D13, "Apple~*") (exactly “Apple*”) |
1 |
=COUNTIF(D2:D13, "*?") |
12: every cell with at least one character |
Wildcards skip numbers
Found in testing: in a column of amounts, =COUNTIF(E2:E13, "*0*") returned 1, though ten of the twelve amounts contain a 0. The one it found was “100” stored as text. Wildcards only match text, so real numbers were ignored.
To search digits inside numbers, turn them into text first: =SUMPRODUCT(--REGEXMATCH(E2:E13&"", "0")) returned 10. Joining &"" turns each value into text before REGEXMATCH reads it. If numbers in your sheet are stored as text by accident, see convert text to numbers.