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
Sheet2
Sheet3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]{=SUM(SUBTOTAL(9,INDIRECT(A1:A2&"!"&ADDRESS(4,3))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.