3D SUM INDIRECT problem...

[poolio]

I am trying to return the SUM of a variable cell across multiple worksheets... This 3D formula works

=SUM('March 2012:December 2013'!E27)

I wanted the formula on the next row to ref F27 then G27 etc... (which I can't just drag down) so thought I'd try to "assemble" the formula I wanted using the INDIRECT function...

I tried this formula which doesn't seem to work...

=SUM(INDIRECT("'"&E6&":"&E27&"'!E27"))

NOTE: the script in the INDIRECT brackets returns the text string... 'March 2012:December 2013'!E27

Can INDIRECT not handle multisheet references?

All help gratefully received
JP

 

[Paul_C]

Dear pgc01,

Thank you so much for the post. You may be surprised that people respond to you after 2 years from your initial post. I was trying to do INDIRECT with multiple sheets and came across your post. The formula works. In your formula, I can understand most part but could not understand the function of N in the section =sum(N(INDIRECT. Are you able to explain that or give some direction to do my own research?

Best Regards

Paul_C

Hi


This is an example, I set up the solutions with the inputs and the list with the sheet names in Sheet1.

The formula uses your 2 inputs, E6 and E27 that contain the names of the start and end sheets.

I wrote the names of the sheets that contain the numbers to be added in I2:I10

I have in

Sheet3!E27: 3
Sheet4!E27: 4
Sheet5!E27: 5

In B3:

=SUM(N(INDIRECT("'"&INDEX(I2:I10,MATCH(E6,I2:I10,0)):INDEX(I2:I10,MATCH(E27,I2:I10,0))&"'!E27")))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.


[TABLE="width: 2"]
<tbody>[TR]
[TH] [/TH]
[TH="align: center"]A[/TH]
[TH="width: 30, align: center"]B[/TH]
[TH="width: 30, align: center"]C[/TH]
[TH="width: 30, align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="width: 30, align: center"]F[/TH]
[TH="width: 30, align: center"]G[/TH]
[TH="width: 30, align: center"]H[/TH]
[TH="align: center"]I[/TH]
[TH="width: 30, align: center"]J[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Sheet2[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: left"]Total[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Sheet3[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Sheet4[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Sheet5[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Sheet3[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Sheet6[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Sheet7[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Sheet8[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Sheet9[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Sheet10[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Sheet5[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="colspan: 11"] [Book1]Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

Hi Paul
Welcome to the board

This is my take on this subject.

N(Indirect(...))

The Indirect() in this case would return an array of references. As you know, in an excel formula you can only have an array of constants, for ex.

{1,2,3}

you cannot have an array of anything else, like an expression or a reference, for ex. this

{A1,A4,A5}

is not allowed.

As a workaround, since I cannot have the array with the references returned from Indirect, I used the N() as a de-referencing operator, extracting the values of the ranges returned from Indirect() and building the array with those values.

A simple (a bit ridiculous) example

=SUMPRODUCT(INDIRECT({"A1","C4"}))

This Indirect() would return an array with the references, {A1,C4} which is not allowed. If you use

=SUMPRODUCT(N(INDIRECT({"A1","C4"})))

The N() will extract the values and you'll have something like

=SUMPRODUCT({value of A1,value of C4})))

which will calculate OK (I'm assuming A1 and C4 have number values).

Does this make sense to you?

 

[Paul_C]

Thanks again for the prompt reply. I have used Excel for a long time but I have to admit never used array formulas before. Never come across using the operator "N" but it is my lack of advanced Excel skills. I can follow your logic but on my own cannot come up with a formula like that. I'll do some reading on array formulas so that your formula and its functionality will become more clearer. Thanks again for your time. Hope you will have a Merry Christmas and a Very Happy New Year.
Best Regards
Paul_C

Hi Paul
Welcome to the board

This is my take on this subject.

N(Indirect(...))

The Indirect() in this case would return an array of references. As you know, in an excel formula you can only have an array of constants, for ex.

{1,2,3}

you cannot have an array of anything else, like an expression or a reference, for ex. this

{A1,A4,A5}

is not allowed.

As a workaround, since I cannot have the array with the references returned from Indirect, I used the N() as a de-referencing operator, extracting the values of the ranges returned from Indirect() and building the array with those values.

A simple (a bit ridiculous) example

=SUMPRODUCT(INDIRECT({"A1","C4"}))

This Indirect() would return an array with the references, {A1,C4} which is not allowed. If you use

=SUMPRODUCT(N(INDIRECT({"A1","C4"})))

The N() will extract the values and you'll have something like

=SUMPRODUCT({value of A1,value of C4})))

which will calculate OK (I'm assuming A1 and C4 have number values).

Does this make sense to you?

 

[pgc01]

Thanks for the feedback. Merry Christmas to you too!