Weighted Average across worksheets

[AS]

I am aware of the technique using sumproduct to calculate a weighted average
figure, but this doesn't appear to work across worksheets.
I have 12 worksheets named P1 - P12 and am trying to calculate a weighted
average of the cells K2 and L2 on each sheet
The formula =SUMPRODUCT('P112'!L2,'P112'!K2) / SUM('P112'!K2) returns
#REF!
Can anyone help please.
Allan

 

[Lori]

Sumproduct cannot be used with 3D-references. Try instead:

=SUM(SUBTOTAL(6,INDIRECT("'"&sheets&"'!K2:L2"))) / SUM('P112'!K2)

where "sheets" refers to a range of cells containing the sheet names:
P1,P2,P3,...

 

[AS]

Thanks for that, works fine as an array formula.
As an extension to the problem, I also want to do the same for the cells K2
and M2 on each sheet.
Can you do that for non contiguous cells?

Allan

 

[Lori]

For non-contiguous cells, you could try instead:

=SUMPRODUCT(N(INDIRECT("'"&sheets&"'!K2")),N(INDIRECT("'"&sheets&"'!K2")))

(divided by the sum.)

 

[AS]

It took me a while to understand it ........ one of the K2 references should
be M2
Changed that and works great.
I prefer this solution whether contiguous or not because it seems to work as
an ordinary formula as well as an array entry formula.
Don't pretend to understand the intricacies !!
Thanks again
Allan

 

[Lori]

Thanks for the reply, glad it works and you corrected the typo.