Excel Formula to sum cell over multiple sheets

0
votes

I have an Excel document that I need to have a totals sheet which takes in a specific cell over multiple sheets. It also needs to be expandable so I can just add the sheet name to a row or something and it will add it into the formula and the total.

Just a row like this or something similar which could be used to add more sheets in future.

To give you an example currently, I am using a simple SUM function but it's not easily expandable: =SUM('Sheet1'!A6,'Sheet2'!A6,'Sheet3'!A6)

I have had a look at INDIRECT but I can't find a way of having it expand to the length of an array eg. something like this: =SUM(INDIRECT(H3:H8,"!A6"))

Would return all the values of A6 across each sheet named in H3:H8 evaluating like: =SUM(INDIRECT(H3,"!A6"),INDIRECT(H4,"!A6"),INDIRECT(H5,"!A6")...)

If anyone has any ideas of what I can use to achieve this, it would be very helpful!

2
excelexcel-formula

2 Answers

0
votes

If you want a dynamic list of sheets for your sum formula, you may

  1. Put the names of the sheets across which you would like to sum your cell(s) on a separate range of cells organized as a column range:

Sheet1

Sheet2

...

  1. Convert the cells with the names of sheets into a table (select your range, then on the main menu panel select "Insert - Table").

  2. Name the created table list (i.e. "List_of_Sheets")

  3. To sum use the formula: =SUMPRODUCT(SUM(INDIRECT("'"&List_of_Sheets&"'!A6")))

  4. You may change the names of sheets in your table or add the new ones at the end of the table

0
votes

Try this, SUM(Sheet1:Sheet3!A6)

You can add a Sheet named „Start“ and one named „End“. Place all sheets you want to sum between the both. So you can use

SUM(Start:End!A6)