SUMPRODUCT for 3D values

[Alex J]

All (especially Frank Kabel, I guess)

I have done some googles on the newsgroup on the topic of SUMPRODUCT, and
frankly am a little flabberrgasted. So, could some pundit advise if the
following is possible, please?

I am using 3D sums (like =SUM(Sheet1:Sheet4!A1) to sum accross multiple
sheets. What I would like to use an array (1,1,1,0) which indicates whether
the values from a particular sheet are included in the sum, or not. Hence
the idea of using SUMPRODUCT, resulting web search, confusion, and newsgroup
question.

Any takers on this one?

Alex J

 

[Frank Kabel]

Hi Alex
some more information would be helpful
Just give an example of what you're trying to achieve

 

[Tom Ogilvy]

SumProduct does not support 3D references.

 

[Alex Jankowski]

Frank,

The problem is:
I have a series of sheets (up to 45 right now), each corresponds to a
project.

Sheet names and positions are:
RollupSht, &Start, Proj1, Proj2, etc, &End

An overall rollup sheet adds values from corresponding cells on each
project sheet to the rollup sheet. No problem: SUM('&Start':'&End'!A1)

What I wish to do is "filter out" some of the sheets from the summation
to the summary page by using SUMPRODUCT. I would form an array of 1's
and 0's based on which sheets I want to include in the rollup.
(conceptually Sheet1!A1*1 + Sheet2!A1*0 + Sheet3!A1*1 + ...)

I already have a method to achieve this by moving sheets in and out of
the summation range (after sheet &End), but this is slow. I could also
do zero multiplication locally on the sheet and add THAT, but I thought
SUMPRODUCT might do the job more elegantly, and with less memory impact.

Let me know if you think there is hope for this one.

Alex J

 

[Frank Kabel]

Hi
and there do you want to put your criteria?

 

[Alex Jankowski]

Array would be a worksheet range, a single cell on each project sheet,
or an array saved as a workbook name.

Regards,
Alex J

 

[Frank Kabel]

Hi
so lets assume you would put 'X' in cell A1 on each sheet you want to
sum and you want to sum cell B1 on each of these sheets.
1. Put a list of all worksheets on your summary sheet. e.g. in cells
X1:X10
2. Use the following formula:
=SUMPRODUCT(SUMIF(INDIRECT("'" & X1:X10 & "'!A1"),"X",INDIRECT("'" &
X1:X10 & "'!B1")))

 

[Tom Ogilvy]

Note that
=SUM(SUMIF(INDIRECT("'" & X1:X10 & "'!A1"),"X",INDIRECT("'" &X1:X10 &
"'!B1")))

Will work as well if you array enter it.

 

[Alex Jankowski]

Thanks to both Frank & Tom. I will evaluate and post back.

Regards,
Alex J