Help with 'Selective' SUMPRODUCT Formula

[99Declan]

Hello,

I have a large table of data and want to calculate a weighted mean for certain values which match a criteria. To do this for the whole data I've got =SUMPRODUCT(D:D, F:F)/SUM(F:F) which works perfectly. However, I'd now like this to apply to only certain values, eg:

B |C |D
x |1 |0.1
y |2 |0.1
y |5 |0.1
y |4 |0.2
z |2 |0.2
z |1 |0.2
x |0 |0.5
y |0 |0.5
x |1 |0.5
x |2 |0.5

And have it only perform the formula I mentioned earlier on say, values in the same row as an x.



Best,

Declan

[FDibbins]

Instead of SP, try AVERAGEIFS()...
=averageifS(D:D, B:B,"x")
(not sure where column F comes in there?)

Also, try to avoid using full-column references in SP, it will slow your file down