# using indirect for a 3d sum range

#### jaymri

##### New Member
I am trying to use the indirect function to sum over a 3d range.

I would like to use indirect instead of :
=sum(Sheet2!:Sheet5!:b10)

however even just typing in the text does not work
=sum(indirect("Sheet2!:Sheet5!:b10"))
putting Sheet names within ''s also doesn't seem to work.
=sum(indirect("'Sheet2'!:'Sheet5'!:b10"))

a1="Sheet2"
a2="Sheet5"
=sum(indirect(a1\$&"!:"&a2&"!:b10"))

If you have a solution that would work on Sheet2!A7:Sheet5!:b10 that would be great.

I believe that I have tried dozens of variations of double and single quotes. If anyone can help, thanks,

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Derek Brown

##### Well-known Member
Note quite sure that I understand, from your examples, what you are trying to achieve but here are some examples that may help.

To sum the same cell on the range of worksheets:
Code:
``=SUM(Sheet2:Sheet5!B10)``

To sum a range of cells on a range of worksheets:
Code:
``=SUM(Sheet2:Sheet5!A1:B10)``

To indirectly sum cells on a range of worksheets:
Code:
``=SUM(INDIRECT(Sheet2!B10),INDIRECT(Sheet3!B10),INDIRECT(Sheet4!B10),INDIRECT(Sheet5!B10))``

but note that, for the INDIRECT to work then cell B10 must include a reference to the sheet as well as the cell. eg Cell B10 on Sheet4 would contain:
Code:
``Sheet4!A1``
to indirectly SUM the content of, in this example, Cell A1

#### jaymri

##### New Member
thanks, but comma separated 2d sum ranges is not what I need to do. I understand your solution.

I need to sum over a flexible 3d range

=sum(Sheet2!:Sheet5!:b10) and/or =sum(Sheet2!A5:Sheet5!:b10)

assume my numbers are in cells B10 through B20. and are on sheet FirstSheet through sheet LastSheet.

taking it in steps

=sum(FirstSheet:LastSheet!B12) works

=sum(indirect("LastSheet!B12)) works

=sum(indirect("'FirstSheet'!B10:B20)) works

=sum(indirect("'FirstSheet'!B10:'LastSheet'!B20")) #REF

I have tried many combinations of " and '

I put the text in the example above instead of what I will really use when it works, something like
=sum(indirect(A5))
A5="'FirstSheet'!B10:'LastSheet'!B20" or

A5= "'"&A6&"'!B10:'&A7&"'!B20" where
A6="FirstSheet" and A7="LastSheet"

I hope that I have made a clearer example.

#### Domenic

##### MrExcel MVP
First, list your sheet names in a range of cells, let's say A2:A5. Then, let B2 contain the name of the first sheet, and C2 the name of the last sheet. Then try the following formulas...

=SUMPRODUCT(--(N(INDIRECT("'"&INDEX(\$A\$2:\$A\$5,MATCH(B2,\$A\$2:\$A\$5,0)):INDEX(\$A\$2:\$A\$5,MATCH(C2,\$A\$2:\$A\$5,0))&"'!B10"))))

=SUMPRODUCT(SUMIF(INDIRECT("'"&INDEX(\$A\$2:\$A\$5,MATCH(B2,\$A\$2:\$A\$5,0)):INDEX(\$A\$2:\$A\$5,MATCH(C2,\$A\$2:\$A\$5,0))&"'!A5:B10"),"<>"))

Note, however, depending on how your sheets are named, it may be possible to dispense with having to list them in a range of cells.

Hope this helps!

#### jaymri

##### New Member
thanks, but I really do not think it will work for me. but I'll try.

is INDIRECT unable to pass on a 3d range?

I really would need something very simple like

=sum(indirect("'FirstSheet'!B10:'LastSheet'!B20"))

=sum(indirect(A5))
A5="'FirstSheet'!B10:'LastSheet'!B20" or

A5= "'"&A6&"'!B10:'&A7&"'!B20" where
A6="FirstSheet" and A7="LastSheet"

#### vane0326

##### Well-known Member
Put your first worksheet (2) in cell A1 and your last worksheet (5) in cell A2.

Then Try...

Insert > Name > Define

Name: Array

Refers to:

=EVALUATE(EVALUATE("if("&MID(GET.CELL(6,!A3),6,2 55)))

Click Ok

Then input formula in cell A3

=IF(1,Evaluate,"sum('"&A1&":"&A2&"'!B10)")

Hope it helps!

Replies
1
Views
330
Replies
7
Views
142
Replies
5
Views
251
Replies
3
Views
182
Replies
3
Views
201

1,181,647
Messages
5,931,210
Members
436,784
Latest member
amuljono

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back