Summing Multiple pages using the Address Function to specify the Cell

[caius102]

Hello, I've spent hours searching and can't find why what I'm trying to do won't work.

I want to sum the same cell accross multiple pages but using the Address function to determine the cell to sum.

SUM(First:Last!$C$4) works, and ADDRESS(4,3) returns a value of $C$4, but SUM(First:Last!ADDRESS(4,3)) just says 'There's a problem with this Formula'.

What I want to be able to do is use the sum function above, but calculate the address of the cell ($C$4 in the example) based calculations to determine the column and row of the cell that is being summed across the pages.

 

[Eric W]

Welcome to the board.

The Excel 3-D functions are pretty limited. They don't have a lot of options. In particular, they don't work with INDIRECT or OFFSET. About the best option I can suggest is:

	A	B
1	Sheet2	10
2	Sheet3
3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B1 {=SUM(SUBTOTAL(9,INDIRECT(A1:A2&"!"&ADDRESS(4,3))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put a list of sheets you want to sum in A1:A2, then use the array formula in B1. If you want to leave some empty cells on the bottom of the sheet list, use this version:

=SUM(IFERROR(SUBTOTAL(9,INDIRECT(A1:A5&"!"&ADDRESS(4,3))),0))
with Control+Shift+Enter.

If neither of those work for you, then you'll probably need a UDF.