Spreadsheet Practice

How to count cells that do not contain specific text in Excel

Formula (Excel and Google Sheets)

=COUNTIF(A2:A11,"<>*"&E1&"*")

Put "not equal" in front of the wildcard pattern: =COUNTIF(A2:A11,"<>*"&E1&"*").

Result on the example
7 in E2
Works in
Every current version
Google Sheets
Same formula

See it work

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

How it works

"*cable*" matches any name containing "cable", and putting <> in front turns it around: count everything that doesn't match. Three names contain "cable" (in any case), so seven don't.

=COUNTIF(A2:A11,"<>*"&E1&"*")

Result7

  1. A2:A1110 rows × 1 col: "Wireless mouse", "USB-C cable 1m", "Wireless keyboard", "HDMI cable 2m", …

    A range: 10 cells in one column.

  2. E1"cable"

    The value in E1.

  3. "<>*"&E1"<>*cable"

    The & operator joins as text.

  4. "<>*"&E1&"*""<>*cable*"

    The & operator joins as text.

  5. COUNTIF(A2:A11,"<>*"&E1&"*")7

    COUNTIF: Counts cells that meet one condition.

    • rangeA2:A1110 rows × 1 col: "Wireless mouse", "USB-C cable 1m", "Wireless keyboard", "HDMI cable 2m", …
    • criteria"<>*"&E1&"*""<>*cable*"

In Google Sheets

The same formula works in Google Sheets: =COUNTIF(A2:A11,"<>*"&E1&"*"). We ran it in Google Sheets mode on the same data and it returns 7.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT(--ISERROR(SEARCH(E1,A2:A11)))7SEARCH fails where the text is missing, so counting the errors counts the cells without it.
=ROWS(A2:A11)-COUNTIF(A2:A11,"*"&E1&"*")7All rows minus the ones that contain the text.

Common mistakes

  • Blank cells don't contain the text, so they are counted. Add ,A2:A11,"<>" in COUNTIFS to skip them.
  • The match ignores case. For a case-sensitive version use =SUMPRODUCT(--ISERROR(FIND(E1,A2:A11))).

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 do not contain specific text

In E2, count the products whose name does not contain the text in E1.

Questions

How do I exclude two words?

Use COUNTIFS with two "<>" patterns: =COUNTIFS(A2:A11,"<>*cable*",A2:A11,"<>*wireless*").

How do I list the cells that don't contain the text?

In Excel 365 or Google Sheets: =FILTER(A2:A11,ISERROR(SEARCH(E1,A2:A11))).

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.