Spreadsheets

Total expenses for a financial year with SUMIFS and EDATE

Total a whole April to March year with one SUMIFS formula, a single start-date cell and EDATE for the end date, then split it by category.

Editorial illustration for Total expenses for a financial year with SUMIFS and EDATE

To total expenses for an Indian financial year, put the start date (say 01/04/2025) in one cell, let EDATE add twelve months to get the next start date, and feed both to SUMIFS as a greater-than-or-equal and a less-than test. One formula gives the year total, and the same pattern with a category column gives each category. Change the start date next year and everything re-points itself.

The layout: one input cell, one derived cell

Assume your expense log is on a sheet with the date in column A, the category in column B and the amount in column C, with data from row 2 down to about row 500. On a summary sheet, set up two cells:

  • E1 holds the financial year start, typed as a real date: 01/04/2025.
  • E2 holds the next year's start: =EDATE(E1,12). It shows 01/04/2026 once formatted as a date.

EDATE moves a date forward by whole months, so twelve months from 1 April is always the next 1 April. You never type the end date by hand, which removes the classic mistake of writing 31/03 and then forgetting that an entry stamped late on 31 March was left out.

What is the SUMIFS formula for the whole year?

In any cell, enter:

=SUMIFS($C$2:$C$500,$A$2:$A$500,">="&$E$1,$A$2:$A$500,"<"&$E$2)

Read it left to right: add up the amounts in C2:C500 where the date is on or after the start and strictly before the next start. The ampersand glues the comparison symbol to the date stored in the cell. Using "<" with the next 1 April, rather than "<=" with 31 March, means an expense recorded with a time of day on 31 March is still counted. The dollar signs lock the ranges so the formula can be copied around safely. This works the same in Excel and Google Sheets.

How do I get the total for each category?

List your categories down column D from D5, then in E5 enter:

=SUMIFS($C$2:$C$500,$B$2:$B$500,$D5,$A$2:$A$500,">="&$E$1,$A$2:$A$500,"<"&$E$2)

Copy it down beside each category. It is the earlier formula with one more pair of arguments: the category range and the category name sitting in column D. Add a check row under the list: =E3-SUM(E5:E12), where E3 holds the year total. If it shows anything other than 0, some expenses sit under a category you have not listed, or a category is spelt slightly differently in the log.

A worked example with made-up numbers

Say the financial year 2025-26 produces these totals. Rent is ₹18,000 for twelve months, which is ₹2,16,000. The rest are round invented figures, only to show the arithmetic.

CategoryFY total
Rent₹2,16,000
Groceries₹84,500
Transport₹31,200
Bills and utilities₹39,800
Everything else₹20,000
Year total₹3,91,500

Adding the five lines: 2,16,000 + 84,500 = 3,00,500; plus 31,200 = 3,31,700; plus 39,800 = 3,71,500; plus 20,000 = 3,91,500. If your check cell shows 0, the category table and the year total agree.

Why not just filter by month and add them up?

You can, but twelve separate monthly sums leave twelve places for an error and need rebuilding each year. With one start-date cell you can also look back: type 01/04/2024 in E1 and the whole summary shows last year. Put two summary blocks side by side, one pointing at E1 and another at a second start cell, and you get a year-on-year comparison without touching a formula.

What if my year does not start in April?

Nothing else changes. A business year starting 1 July uses 01/07/2025 in E1, and EDATE still gives the next 1 July. For a calendar year, use 01/01. If you want a label that updates, add a cell with =TEXT(E1,"mmm yyyy")&" to "&TEXT(EDATE(E2,-1),"mmm yyyy") so the sheet says Apr 2025 to Mar 2026.

Before you rely on it

  1. Make sure every date in column A is a real date and none lies outside the year by a typing slip such as 2052.
  2. Keep category names identical in the log and in column D; a trailing space counts as a different name.
  3. Extend the $500 row limit if your log grows beyond it.
  4. Do the sum-of-categories check before sharing any total.

When keeping the log by hand and fixing categories every month becomes the real work, SpendixAI can take statement uploads, suggest categories you review, and download a CSV of the year for your sheet. Rules and limits for tax filing change, so for anything that goes into a return, confirm the figures with your CA.

SPENDIXAI JOURNAL

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

Explore your reports