Spreadsheets

Sum one category across monthly sheet tabs in Excel

Total one category across a workbook with a tab per month, using 3D SUM for a fixed cell and SUMIF with INDIRECT and a list of tab names.

Editorial illustration for Sum one category across monthly sheet tabs in Excel

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

TabFood total (₹)Source
Apr9,200SUMIF on tab Apr
May10,100SUMIF on tab May
Jun8,700SUMIF on tab Jun
Quarter total28,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.
SPENDIXAI JOURNAL

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

Explore your reports