If your workbook has one tab per month with the same layout, a 3D SUM adds a fixed cell across all tabs, such as =SUM(Apr:Mar!C20). To total a category that sits on different rows in each tab, use SUMIF with INDIRECT and a list of tab names. Both work in Excel. Google Sheets does not support 3D references.
How does a 3D SUM work?
A 3D reference points at the same cell or range on a run of sheets. =SUM(Apr:Mar!C20) adds cell C20 on every tab from Apr to Mar, in the order the tabs appear at the bottom of the workbook. To build it, type =SUM( then click the Apr tab, hold Shift and click the Mar tab, then click C20 and close the bracket. If tab names have spaces, Excel adds quotes for you, for example =SUM('Apr 25:Mar 26'!C20).
This works when each tab has a total in the same cell. Say C20 on every tab holds that month's Food total. Then the year's Food total is one formula. Anything you insert between the first and last tab is included, and anything you drag outside them is dropped. A common surprise is a summary tab sitting between months.
What if the category is on a different row in each tab?
Then a fixed cell will not do. Instead, use SUMIF on each tab's list of transactions. Say each tab has categories in B2:B500 and amounts in C2:C500. List the tab names in H2:H13 exactly as they appear: Apr, May and so on to Mar. For one tab, the formula in I2 is:
- =SUMIF(INDIRECT("'"&H2&"'!$B$2:$B$500"),"Food",INDIRECT("'"&H2&"'!$C$2:$C$500"))
INDIRECT turns the text built from the tab name into a real range. The single quotes around the tab name handle spaces. Fill I2 down to I13 and put =SUM(I2:I13) underneath. Each month is visible, which is easier to check than one giant formula.
Is there a one-cell version?
In Excel it is possible to total every tab in a single cell with SUMPRODUCT around the SUMIF: =SUMPRODUCT(SUMIF(INDIRECT("'"&$H$2:$H$13&"'!$B$2:$B$500"),"Food",INDIRECT("'"&$H$2:$H$13&"'!$C$2:$C$500"))). It saves a column but is harder to debug. If a tab name is misspelt, the entire formula returns #REF!, and you cannot see which one. For a household workbook the per-month column is the better choice.
Worked example with invented numbers
| Tab | Food total (₹) | Source |
|---|---|---|
| Apr | 9,200 | SUMIF on tab Apr |
| May | 10,100 | SUMIF on tab May |
| Jun | 8,700 | SUMIF on tab Jun |
| Quarter total | 28,000 | =SUM(I2:I4) |
Adding ₹9,200 + ₹10,100 + ₹8,700 gives ₹28,000. Once all twelve tab names are in the list, the same SUMIF column gives the financial year's total from April to March.
What are the limits of INDIRECT?
- It is volatile, so it recalculates whenever anything changes. In a small workbook you will not notice.
- If you rename a tab, the formula does not follow. The name in the list has to be edited by hand.
- INDIRECT does not read from closed workbooks, so keep everything in one file.
- A typo in the list shows #REF!, which is helpful, because you notice it.
Whichever route you use, build the first two tabs by copying a clean template tab, not by editing by hand. Identical layouts are what make 3D sums and INDIRECT work. If one month has a different column order, its numbers will quietly land in the wrong place, and no formula will warn you.
When a sheet stops being enough
If every new month means copying a tab and fixing the totals, SpendixAI keeps every imported transaction in one history, with reports you can filter and group by category and period, and a CSV download if you still want the numbers in a sheet.
Before you finish
- Tab names in the list match the real tab names exactly.
- The same layout on every tab, with data from row 2.
- The total of the month column matches a manual sum for one category.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


