sumproduct for multiple sheets

[sarahbenoit]

I am trying to create a weighted average on one sheet calculated from
several other sheets. I need to use the product of just two cells on
each sheet for the weighted average. I know I cant use sumproduct in
3D, is there another way around this problem?

Thanks,

Sarah

 

[Bob Phillips]

=SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N(INDIRECT("Sheet"&ROW(1:3)&"!B1")))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[sarahbenoit]

HI Bob,
Thank you for the prompt reply, I am still having some trouble and
would really appreciate some help. I can seem to figure out how to
properly apply the formula.
For "Sheet" do input the series of sheets that I would be gathering
data from or is this just code?
(1:3) is this for the sheet names? A1 and B1 are these the cells I will
be using as a source for the data? Is N determined by the data source
or do I need to identify it?

=SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N(INDIRECT("Sheet"&ROW(1:3)¬&"!B1")))

Basically I have several sheets KState, Michigan, NYU, UCLA and each
has a number of employees (5,6,7,8) and the mean salary(85000,
90000,75000, 80000), I would like to get the weighted mean of these on
a separate sheet. Any further help would be greatly appreciated.
Thanks again,
Sarah

 

[vezerid]

You can place the sheet names in an array of cells. In the following
formula I assume 8 sheet names are in cells K1:K8. Then you can use the
following variant of Bob's formula:

=SUMPRODUCT(N(INDIRECT(K1:K8&"!A1")),N(INDIRECT(K1:K8&"!B1")))

HTH
Kostis Vezerides

 

[Bob Phillips]

You can even use a constant array

=SUMPRODUCT(N(INDIRECT({"KState","Michigan,"NYU","UCLA"}&"!A1")),N(INDIRECT(({"KState","Michigan,"NYU","UCLA"}&"!B1")))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


You can place the sheet names in an array of cells. In the following
formula I assume 8 sheet names are in cells K1:K8. Then you can use the
following variant of Bob's formula:

=SUMPRODUCT(N(INDIRECT(K1:K8&"!A1")),N(INDIRECT(K1:K8&"!B1")))

HTH
Kostis Vezerides

 

[vezerid]

Hi Bob! (long time...)

I presume the formula runs faster with array constants? And maybe
intermediate with a named constant? Good idea for testing later tonight
at home...

Good night
Kostis

 

[Bob Phillips]

Hi Kostis,

How are you? I bet it's nicer where you are than here. 0C, but feels
like -4.

I would have thought it would be quicker if the list is in cells rather than
inline constant, less evaluating to be done. And more maintainable, but not
as easily readable.Your results should be interesting.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


Hi Bob! (long time...)

I presume the formula runs faster with array constants? And maybe
intermediate with a named constant? Good idea for testing later tonight
at home...

Good night
Kostis