How to sum if a cell does not contain specific text in Excel
Formula (Excel and Google Sheets)
=SUMIF(A2:A9,"<>*"&E1&"*",B2:B9)Put "<>" in front of the wildcard pattern: =SUMIF(A2:A9,"<>*"&E1&"*",B2:B9).
- Result on the example
- $291.85 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
"*transfer*" matches any text containing "transfer", in any case. Putting <> in front turns it around: SUMIF adds the rows that do not match. That leaves the real spending and drops the money moved between accounts.
=SUMIF(A2:A9,"<>*"&E1&"*",B2:B9)Result291.85
A2:A98 rows × 1 col: "Card payment - Fuel Stop", "Transfer to savings", "Card payment - Market", "Online transfer to J. Kim", …A range: 8 cells in one column.
E1"transfer"The value in E1.
"<>*"&E1"<>*transfer"The & operator joins as text.
"<>*"&E1&"*""<>*transfer*"The & operator joins as text.
B2:B98 rows × 1 col: $58.40, $500.00, $126.85, $75.00, …A range: 8 cells in one column.
SUMIF(A2:A9,"<>*"&E1&"*",B2:B9)291.85SUMIF: Adds values where the range meets a condition.- range
A2:A98 rows × 1 col: "Card payment - Fuel Stop", "Transfer to savings", "Card payment - Market", "Online transfer to J. Kim", … - criteria
"<>*"&E1&"*""<>*transfer*" - sum_range
B2:B98 rows × 1 col: $58.40, $500.00, $126.85, $75.00, …
- range
In Google Sheets
The same formula works in Google Sheets: =SUMIF(A2:A9,"<>*"&E1&"*",B2:B9). We ran it in Google Sheets mode on the same data and it returns $291.85.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(ISERROR(SEARCH(E1,A2:A9))*B2:B9) | $291.85 | SEARCH errors when the word is missing, so ISERROR is TRUE for the rows to keep. Swap SEARCH for FIND to make it case-sensitive. |
Common mistakes
- The wildcard also matches inside longer words: "art" excludes "Smart TV". Add spaces around the word or use a category column if that matters.
- Blank descriptions don't contain the word, so their amounts are included.
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.
Sum if a cell does not contain specific text
E2, total the amounts whose description does not contain the word in E1.Questions
How do I exclude two different words?
Use SUMIFS with one condition per word: =SUMIFS(B2:B9,A2:A9,"<>*transfer*",A2:A9,"<>*refund*").
How do I sum only the rows that do contain the word?
Drop the <>: =SUMIF(A2:A9,"*"&E1&"*",B2:B9).
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.