How to sum if a cell contains specific text in Excel
Formula (Excel and Google Sheets)
=SUMIF(A2:A9,"*"&E1&"*",B2:B9)Use SUMIF with wildcards around the text: =SUMIF(range,"*"&text&"*",sum_range).
- Result on the example
- $1,228 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
The asterisks are wildcards for "any characters", so "*"&E1&"*" matches the word at the start, middle or end of the name. The match ignores case, which is why "Travel" and "travel" both count.
=SUMIF(A2:A9,"*"&E1&"*",B2:B9)Result1228
A2:A98 rows × 1 col: "Travel - flights", "Office supplies", "Travel - hotel", "Software license", …A range: 8 cells in one column.
E1"travel"The value in E1.
"*"&E1"*travel"The & operator joins as text.
"*"&E1&"*""*travel*"The & operator joins as text.
B2:B98 rows × 1 col: $640, $85, $410, $1,200, …A range: 8 cells in one column.
SUMIF(A2:A9,"*"&E1&"*",B2:B9)1228SUMIF: Adds values where the range meets a condition.- range
A2:A98 rows × 1 col: "Travel - flights", "Office supplies", "Travel - hotel", "Software license", … - criteria
"*"&E1&"*""*travel*" - sum_range
B2:B98 rows × 1 col: $640, $85, $410, $1,200, …
- 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 $1,228.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(ISNUMBER(SEARCH(E1,A2:A9))*B2:B9) | $1,228 | Same result. Swap SEARCH for FIND to make it case-sensitive. |
Common mistakes
- Without the asterisks, SUMIF only adds names that are exactly "travel".
- Wildcards match inside longer words too: "travel" also matches "Client travel gifts" here, and "art" would match "Start". Add spaces or use exact categories when that matters.
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 contains specific text
E2, add up the amounts whose expense name contains the word in E1.Questions
How do I sum cells that start with a word?
Put the asterisk only at the end: "travel*".
Does SUMIFS support wildcards too?
Yes. =SUMIFS(B2:B9,A2:A9,"*"&E1&"*") gives the same total and lets you add conditions.
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.