Expense categories

A simple sheet to track DMart and supermarket spending

Track DMart and other supermarket bills in a simple sheet with a SUMIFS formula, then see when a spreadsheet stops being enough for the monthly total.

Editorial illustration for A simple sheet to track DMart and supermarket spending

To track DMart spending in a sheet, make four columns: date, store, amount and how you paid. Add one row per bill, then use SUMIFS to total the store by month. The formula takes five minutes to set up and gives you a running figure for your monthly stock-up, without needing any special tool.

Set up the columns

  • Column A: date of the bill.
  • Column B: store name, written the same way every time, such as DMart.
  • Column C: amount in rupees.
  • Column D: payment mode: UPI, card or cash.
  • Column E: a short note, such as monthly stock-up or festival extras.

The formulas that do the work

Put the month's first date in cell G1 and the next month's first date in G2. Then in H1 write the total for one store between those dates. The argument order is sum range first, then each criteria range with its condition.

  • =SUMIFS($C$2:$C$500, $B$2:$B$500, "DMart", $A$2:$A$500, ">="&$G$1, $A$2:$A$500, "<"&$G$2)
  • =COUNTIFS($B$2:$B$500, "DMart", $A$2:$A$500, ">="&$G$1, $A$2:$A$500, "<"&$G$2) gives the number of bills.
  • =H1/MAX(1,H2) gives the average bill, where H2 holds the count and MAX avoids division by zero.

These work in Excel and in Google Sheets. Keep the dates real dates, not text, or the comparison will fail.

Worked example

Say your sheet shows these made-up bills for one month: ₹3,420 on the 3rd, ₹860 on the 12th, ₹1,240 on the 19th and ₹2,110 on the 27th. SUMIFS returns 3,420 + 860 + 1,240 + 2,110 = ₹7,630, COUNTIFS returns 4, and the average bill is 7,630 / 4 = ₹1,907.50. If you budgeted ₹7,000 for the store, you are ₹630 over.

DateStoreAmount (₹)Mode
3 NovDMart3,420UPI
12 NovDMart860UPI
19 NovDMart1,240Card
27 NovDMart2,110UPI

Why do my totals come out zero?

Three usual reasons. The store name has different spellings in different rows, such as Dmart and DMart Ready. The dates were pasted as text and are left-aligned. Or the range stops short of your latest row. Fix the spellings with Find and Replace, convert the dates, and use ranges that run well past your last row, like row 500.

When a sheet stops being enough

If you want every card and UPI debit sorted automatically, a sheet means retyping the statement each month. SpendixAI imports a bank or card statement from PDF, CSV or Excel, lets you review rows before saving, and can apply a rule so the supermarket always lands in Groceries. Cash bills you still add by hand.

How can I see the split by payment mode?

Use the same SUMIFS pattern with the mode column as another criterion. For example, =SUMIFS($C$2:$C$500, $B$2:$B$500, "DMart", $D$2:$D$500, "UPI") totals only the bills paid by UPI. Change "UPI" to "Card" or "Cash" for the others. In the example above, UPI bills come to 3,420 + 860 + 2,110 = ₹6,390 and the card bill is ₹1,240, so the two modes add up to ₹7,630 again. If they do not add up to the store total, a mode was spelled differently in one row.

Can I compare stores in the same sheet?

Yes. Keep one row per bill and change the store name in the formula. To compare, make a small table with store names down a column and the month dates across, and copy the SUMIFS formula into each cell, with the store name taken from the cell on the left rather than typed in, such as $F3. Over a few months you can see whether the monthly stock-up bill and the quick top-up trips behave differently. Do not read price conclusions into it: totals show spending, not which store is cheaper.

Monthly routine

  1. Enter bills within a day of shopping.
  2. On the last day, update the two dates in G1 and G2.
  3. Compare the total with your budget and note the reason for any gap.
SPENDIXAI JOURNAL

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

Explore your reports