For non-contiguous cells, you could try instead:

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

(divided by the sum.)

AS wrote:
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" wrote in message
s.com...
Sumproduct cannot be used with 3D-references. Try instead:

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

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

AS wrote:
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('P1:P12'!L2,'P1:P12'!K2) / SUM('P1:P12'!K2)
returns
#REF!
Can anyone help please.
Allan