Spreadsheets

SUMIF with wildcards to total spending at one merchant

Total everything you spent at one merchant from messy bank narrations using SUMIF with asterisk wildcards, plus the ? and ~ cases explained.

Editorial illustration for SUMIF with wildcards to total spending at one merchant

To total all spending at one merchant when the narration text is messy, put asterisks around the merchant name in a SUMIF criterion: =SUMIF($B$2:$B$500,"*zomato*",$C$2:$C$500). The asterisk stands for any run of characters, so the formula picks up every row whose narration contains the word, wherever it sits in the text. Upper and lower case do not matter.

Why bank narrations need wildcards

A bank narration rarely says just the merchant. A UPI debit can be a long string of reference numbers, a handle and a name, and the same restaurant app may show up with slightly different text from one order to the next. A plain SUMIF with the criterion "Zomato" only matches cells that say exactly Zomato, so it returns 0. Say your sheet has the date in column A, the narration in column B and the amount in column C as a positive number.

How do I build the formula step by step?

  1. Keep narrations in $B$2:$B$500 and amounts in $C$2:$C$500. Both ranges must have the same number of rows.
  2. In cell E2 type just the merchant word, for example zomato.
  3. In F2 enter =SUMIF($B$2:$B$500,"*"&E2&"*",$C$2:$C$500). The & joins an asterisk on each side of whatever E2 holds.
  4. Change E2 to swiggy or bigbasket and F2 recalculates without touching the formula.

The same SUMIF syntax works in Excel and Google Sheets, so a sheet moved between the two keeps working.

Which wildcard pattern should I use?

CriterionMatches cells that...Example narration it catches
"*zomato*"contain the word anywhereUPI-ZOMATO-ORDER 4471
"zomato*"start with the wordZOMATO LIMITED MUMBAI
"*zomato"end with the wordPAID TO ZOMATO
"STORE-??"have exactly two characters after STORE-STORE-A1 and STORE-B2, but not STORE-A12

The question mark stands for exactly one character, which helps when a merchant adds branch or terminal codes of a fixed length. The asterisk stands for zero or more characters.

What if the narration contains an actual asterisk or question mark?

Some card narrations carry an asterisk as a separator. Say a row reads AMZN*MKTP followed by a code. If you write "*AMZN*MKTP*" the middle asterisk is treated as a wildcard, which still works but also matches things you did not intend. To match a real asterisk, put a tilde in front of it: =SUMIF($B$2:$B$500,"*AMZN~*MKTP*",$C$2:$C$500). The tilde works the same way for a real question mark (~?), and to match a tilde itself you write two (~~).

Why does my wildcard total come out too high?

A short search word matches more than you expect. "*ola*" will also catch a narration that happens to contain cola or a shop with ola inside its name. Use a longer, more specific word such as "*ola cabs*", and check by counting the matches with =COUNTIF($B$2:$B$500,"*ola cabs*") and comparing with a text filter set to contains.

Worked example with made-up numbers: three narrations contain zomato, with amounts of ₹420, ₹650 and ₹380, so the wildcard SUMIF gives ₹1,450. If the ₹380 row is a subscription renewal whose narration also says gold, you can leave it out with SUMIFS: =SUMIFS($C$2:$C$500,$B$2:$B$500,"*zomato*",$B$2:$B$500,"<>*gold*"). That returns ₹420 + ₹650 = ₹1,070.

Can I total several merchants at once?

List the merchant words down column E, put the SUMIF from step 3 beside each one, and add a total at the bottom with =SUM(F2:F10). Watch for a transaction that matches two words, because it will be counted twice. When one merchant appears under many different narrations, a sheet gets tedious. SpendixAI lets you add merchant aliases so that different narrations are treated as one merchant, and its reports then group spending without wildcard formulas.

Before you rely on the number

  • Run a text filter on the narration column with the same word and compare the row count.
  • Confirm refunds are not sitting in the same column as positive numbers.
  • Keep the search words in cells, not typed inside formulas, so you can audit them later.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports