Spreadsheets

Sum spending by payment mode (UPI, card, cash) with SUMIFS

Set up a payment-mode dropdown and a small SUMIFS table that shows UPI, credit card, debit card and cash totals for a month, each as a percentage.

Editorial illustration for Sum spending by payment mode (UPI, card, cash) with SUMIFS

Add a Payment mode column with a dropdown list, then build a four-row summary with SUMIFS: =SUMIFS($C$2:$C$500,$D$2:$D$500,G2) for each mode in column G. Divide each result by the grand total to get its share. In a few minutes you can see how much of the month went out through UPI, credit card, debit card and cash.

How should the expense log be laid out?

Keep it plain. Column A holds the date, B a short description, C the amount as a positive number, D the payment mode and E the category if you use one. One row for every payment. The only rule that matters is that column D contains one of a short, fixed list of words, spelled the same way every time. SUMIFS compares text, so UPI and Upi match, but UPI and U.P.I. do not.

How do I add the dropdown?

  1. Select D2:D500.
  2. In Excel choose Data, then Data Validation, set Allow to List, and in Source type UPI,Credit card,Debit card,Cash.
  3. In Google Sheets choose Data, then Data validation, add a rule, pick Dropdown, and enter the same four options.
  4. Turn on the option to reject other entries, so a typo cannot create a fifth mode by accident.

If you pay with a wallet or net banking sometimes, add those options to the list at this point. A mode you add later still works, but rows entered earlier will not have it.

What does the summary table look like?

In G2:G5 type the four modes exactly as in the dropdown. In H2 enter =SUMIFS($C$2:$C$500,$D$2:$D$500,G2) and fill down to H5. In H6 enter =SUM(H2:H5). In I2 enter =H2/$H$6 and fill down, then format column I as a percentage. For one particular month, add date conditions: ,$A$2:$A$500,">="&$K$1,$A$2:$A$500,"<"&EDATE($K$1,1), where K1 holds the first day of the month.

Hypothetical month, with all amounts invented for illustration:

ModeTotal (₹)Share of spend
UPI21,40047.6%
Credit card15,60034.7%
Debit card3,0006.7%
Cash5,00011.1%
All modes45,000100% (rounded)

The shares add to 100.1 because each one is rounded to one decimal place. The working is ₹21,400 / ₹45,000 = 0.4756, and so on for the others.

How do I catch rows with no payment mode?

Rows that were entered but left blank in column D fall out of every SUMIFS, so the four totals would not add up to the whole. Add a check cell: =SUM($C$2:$C$500)-H6. If it shows anything other than zero, some rows have a missing or misspelt mode. Filter column D for blanks to find them.

What can I learn from the shares?

  • A rising card share suggests a bill is coming. Card spends are paid later, so this month's card total shows up in next month's bank balance.
  • A very small cash share may mean cash spends are not being recorded. Compare with what you withdrew from the ATM.
  • A large UPI share made of many small payments is worth looking at by category, because small payments are easy to forget.

The table shows mode, not reason. Pair it with a category summary if you want to know what the UPI money was spent on.

When a sheet stops being enough

If you want this without typing the mode for every row, SpendixAI asks you to pick the account, which can be a bank, a card or a wallet, before you import a statement, and each transaction keeps that account. Cash spends can be added by hand, one at a time or several together.

Your setup checklist

  • Dropdown with exactly the modes you use.
  • Check cell showing zero difference.
  • Credit card bill payments kept out of the spend totals.
  • Cash entries logged the same day, while you remember them.
SPENDIXAI JOURNAL

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

Explore your reports