COUNTIF Contains Text in Google Sheets: Partial Matches and Wildcards

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.

Related guides