Spreadsheet Practice

How to count cells that contain specific text in Excel

Formula (Excel and Google Sheets)

=COUNTIF(A2:A10,"*"&D1&"*")

Use COUNTIF with wildcards: =COUNTIF(range,"*"&text&"*") counts every cell that contains the text anywhere.

Result on the example
5 in D2
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

How it works

An asterisk stands for any characters, including none, so "*"&D1&"*" matches "refund" at the start, the middle or the end of the subject. COUNTIF ignores case, so "Refund" and "refund" both count.

=COUNTIF(A2:A10,"*"&D1&"*")

Result5

  1. A2:A109 rows × 1 col: "Refund not received", "Change delivery address", "Partial refund request", "Login problem after update", …

    A range: 9 cells in one column.

  2. D1"refund"

    The value in D1.

  3. "*"&D1"*refund"

    The & operator joins as text.

  4. "*"&D1&"*""*refund*"

    The & operator joins as text.

  5. COUNTIF(A2:A10,"*"&D1&"*")5

    COUNTIF: Counts cells that meet one condition.

    • rangeA2:A109 rows × 1 col: "Refund not received", "Change delivery address", "Partial refund request", "Login problem after update", …
    • criteria"*"&D1&"*""*refund*"

In Google Sheets

The same formula works in Google Sheets: =COUNTIF(A2:A10,"*"&D1&"*"). We ran it in Google Sheets mode on the same data and it returns 5.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT(--ISNUMBER(SEARCH(D1,A2:A10)))5Same result, and it can be combined with other array conditions. Use FIND instead of SEARCH for a case-sensitive count.
=COUNTIF(A2:A10,D1&"*")1Different resultCounts only subjects that start with the word. Only the subjects that begin with "refund".

Common mistakes

  • Without the asterisks, =COUNTIF(A2:A10,D1) counts only cells that are exactly "refund".
  • To count a literal * or ?, put a tilde before it: "*~?*" counts cells containing a question mark.
  • COUNTIF counts cells, not occurrences. A subject with "refund" twice still counts once.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count cells that contain specific text

In D2, count the tickets whose subject contains the word in D1, anywhere in the text.

Questions

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

Add one COUNTIF per word, like =COUNTIF(A2:A10,"*refund*")+COUNTIF(A2:A10,"*cancel*"). A cell with both words is counted twice; use SUMPRODUCT with OR logic to avoid that.

Is COUNTIF case-sensitive?

No. For a case-sensitive count use =SUMPRODUCT(--ISNUMBER(FIND(D1,A2:A10))).

Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.