Spreadsheets

Net monthly spend with SUMIFS from separate debit and credit columns

Net monthly spend from debit and credit columns using two SUMIFS with start and end dates, plus a 12-row example that ties back to the statement.

Editorial illustration for Net monthly spend with SUMIFS from separate debit and credit columns

Put the first day of the month in one cell, then use two SUMIFS: one for the debit column and one for the credit column, each limited to dates from that day up to but not including the first of the next month. Net spend is debits minus credits. The same pair works for any month you type in.

How is the sheet laid out?

  • A: Date, entered as a true date, not text.
  • B: Narration.
  • C: Debit, D: Credit, both numbers.
  • G1: First day of the month you are analysing, for example 1-Nov-2025, formatted as mmm-yyyy.

Dates matter. If the date column holds text such as "02/11/2025" typed in a different order to your system settings, SUMIFS compares them wrongly and returns 0 with no error. Test one cell with =ISNUMBER(A2).

What are the formulas?

  • Debits in H2: =SUMIFS($C$2:$C$500,$A$2:$A$500,">="&$G$1,$A$2:$A$500,"<"&EDATE($G$1,1))
  • Credits in H3: =SUMIFS($D$2:$D$500,$A$2:$A$500,">="&$G$1,$A$2:$A$500,"<"&EDATE($G$1,1))
  • Net spend in H4: =H2-H3

EDATE($G$1,1) is the same day one month later, so "<"&EDATE(...) means strictly before the first of the next month. Using less-than rather than an end-of-month date avoids the classic problem of a transaction on the 31st with a time stamp falling out of range. The ampersand joins the operator and the date number into one criterion.

A 12-row example that ties to the statement

These are hypothetical rows for November 2025, with credits limited to refunds and reversals so that net spend makes sense.

DateNarrationDebitCredit
02-NovRent18,0000
03-NovUPI reversal0500
04-NovGroceries1,8400
06-NovElectricity2,1500
08-NovMobile recharge3990
09-NovShoe refund01,299
11-NovRestaurant1,1200
14-NovFuel2,0000
17-NovOnline order3,4500
21-NovGroceries2,2600
25-NovStreaming subscription6490
28-NovSociety maintenance3,5000

Debits add up to 18,000 + 1,840 + 2,150 + 399 + 1,120 + 2,000 + 3,450 + 2,260 + 649 + 3,500, which is 35,368. Credits are 500 + 1,299, which is 1,799. Net spend is 35,368 minus 1,799, so 33,569. If the statement's own printed totals say 35,368 and 1,799, your sheet ties.

How do you do the whole financial year?

For the April to March year, put the first month in G1 as 1-Apr and fill months across using =EDATE(G1,1) in the next cell. Anchor the date columns with dollar signs, leave the month cell relative, and copy the two SUMIFS right across twelve columns. A final cell adding the twelve nets should equal the SUMIFS over the whole date range of the year.

What if a month shows zero but you know there is spending?

  • Check that dates are numbers with =ISNUMBER(A2).
  • Check the month cell is a real date and not text.
  • Check the ranges in all three arguments are the same size; SUMIFS returns #VALUE! when they are not.
  • Check the debit column holds numbers, not text with commas.

How do you cut the month by category?

Add a Category column in E and a third condition to the debit formula: =SUMIFS($C$2:$C$500,$E$2:$E$500,"Groceries",$A$2:$A$500,">="&$G$1,$A$2:$A$500,"<"&EDATE($G$1,1)). In the example above, Groceries gives 1,840 + 2,260, which is 4,100. Do the same for each category and the category figures should add back up to the debit total of 35,368, plus the credits if you categorise them too.

A month with no matching rows simply returns 0, which is a useful way to notice a statement you forgot to add.

Closing checklist

  • Dates and amounts as true numbers.
  • Month cell driving both SUMIFS.
  • Net equals debits minus credits.
  • Monthly nets tie to the statement.

SpendixAI offers a library of ready-made reports and a Report builder where you can filter and group by period, when a sheet stops being the right tool.

SPENDIXAI JOURNAL

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

Explore your reports