SUMPRODUCT for 3D values
 

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

SUMPRODUCT for 3D values
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Alex J" schrieb im Newsbeitrag
...
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

SUMPRODUCT for 3D values
 

SumProduct does not support 3D references.

--
Regards,
Tom Ogilvy

"Alex J" wrote in message
...
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

SUMPRODUCT for 3D values
 

Frank,

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

Sheet names and positions a
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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

SUMPRODUCT for 3D values
 

Hi
and there do you want to put your criteria?

--
Regards
Frank Kabel
Frankfurt, Germany

"Alex Jankowski" schrieb im Newsbeitrag
...
Frank,

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

Sheet names and positions a
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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

SUMPRODUCT for 3D values
 

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

Regards,
Alex J

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

SUMPRODUCT for 3D values
 

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")))

--
Regards
Frank Kabel
Frankfurt, Germany

"Alex Jankowski" schrieb im Newsbeitrag
...
Array would be a worksheet range, a single cell on each project
sheet,
or an array saved as a workbook name.

Regards,
Alex J

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

SUMPRODUCT for 3D values
 

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

Will work as well if you array enter it.

--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
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")))

--
Regards
Frank Kabel
Frankfurt, Germany

"Alex Jankowski" schrieb im Newsbeitrag
...
Array would be a worksheet range, a single cell on each project
sheet,
or an array saved as a workbook name.

Regards,
Alex J

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

SUMPRODUCT for 3D values
 

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

Regards,
Alex J

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!