Spreadsheet Practice

How to count cells that contain both of two words in Excel

Formula (Excel and Google Sheets)

=COUNTIFS(A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*")

Use COUNTIFS with one wildcard condition per word on the same range: =COUNTIFS(A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*").

Result on the example
2 in E3
Works in
Every current version
Google Sheets
Same formula

See it work

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

How it works

COUNTIFS counts a cell only when every condition is true, and both conditions can test the same range. The first keeps subjects containing "refund", the second those containing "cancel", so only subjects with both words are counted.

Order doesn't matter: "Cancel order and refund" and "Refund for cancelled order" both count.

=COUNTIFS(A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*")

Result2

  1. A2:A109 rows × 1 col: "Refund not received", "Cancel my subscription", "Cancel order and refund", "Change delivery address", …

    A range: 9 cells in one column.

  2. E1"refund"

    The value in E1.

  3. "*"&E1"*refund"

    The & operator joins as text.

  4. "*"&E1&"*""*refund*"

    The & operator joins as text.

  5. E2"cancel"

    The value in E2.

  6. "*"&E2"*cancel"

    The & operator joins as text.

  7. "*"&E2&"*""*cancel*"

    The & operator joins as text.

  8. COUNTIFS(A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*")2

    COUNTIFS: Counts rows that meet every condition.

    • criteria_range1A2:A109 rows × 1 col: "Refund not received", "Cancel my subscription", "Cancel order and refund", "Change delivery address", …
    • criteria1"*"&E1&"*""*refund*"
    • criteria_range2A2:A109 rows × 1 col: "Refund not received", "Cancel my subscription", "Cancel order and refund", "Change delivery address", …
    • criteria2"*"&E2&"*""*cancel*"

In Google Sheets

The same formula works in Google Sheets: =COUNTIFS(A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*"). We ran it in Google Sheets mode on the same data and it returns 2.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT(ISNUMBER(SEARCH(E1,A2:A10))*ISNUMBER(SEARCH(E2,A2:A10)))2Multiplying the two tests is AND logic. Use FIND instead of SEARCH for a case-sensitive count.

Common mistakes

  • "*refund*cancel*" in one criteria only matches when refund comes before cancel. Separate conditions match either order.
  • Partial words match: "cancel" is found in "cancelled". Add spaces around the word for a stricter test.

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 both of two words

In E3, count the ticket subjects that contain both the word in E1 and the word in E2.

Questions

How do I count cells that contain one word but not another?

Use a "<>" wildcard for the second: =COUNTIFS(A2:A10,"*refund*",A2:A10,"<>*cancel*").

How do I count cells with three words?

Add a third pair: ,A2:A10,"*"&E3&"*".

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