Thread: Sumproduct() challenge: Spanning multiple worksheets

Hello.

I am trying to calculate a weighted average from 2 sets of data: 1 set is an array in a worksheet, and the 2nd set is a range of values that share a specific location across multiple worksheets.

I was trying to use the following (example) formula to calculate the weighted average from these datasets:
= SUMPRODUCT(B4:B6, C4:C6) / SUM(C4:C6)
where the range B4:B6 is the array of values that spans multiple worksheets, and
where C4:C6 is the array of weights on a single worksheet.

I have just learnt that SUMPRODUCT can only handle arrays, and that an array can NOT span multiple worksheets. As such, when B4:B6 was replaced with 'Weather Ticker:Weather Ticker (10)'!M7 , a #REF! error was generated.

As there is considerable data volume on each worksheet, I'd like to know if I can use a workaround or an alternative to get the weighted average (as described above) WITHOUT reorganising my data layout.

Thanks for your help.....
[EDIT] Accidentally "cross-posted" this issue here:
http://www.mrexcel.com/forum/showthr...59#post2815259
and http://www.ozgrid.com/forum/showthre...464#post569464. Will follow here only, as this seems to be the most prolific forum. [/EDIT]