Weighted average using SUMIF and/or SUMPRODUCT
 

My data table looks something like this:
Col A Col B Col C
Product Score Stops
X 98% 100
Y 97% 50
X 75% 150
Z 80% 25
X 99% 200

Totals:
X ?? 450 (using SUMIF for X)
Y ?? 50
Z ?? 25

I would like to get the weighted average by product (such as x) of column B
(score) based on column C (the weight of each row's score based on its % of
X's total stops).

The ?? in Totals should return the sum of
((98%*(100/450))+(75%*(150/450))+(99%*(200/450))) but I am looking for a more
versatile way to do this so when I add products/results in the future it will
include them in the weighted average.

Weighted average using SUMIF and/or SUMPRODUCT
 

With ?? in K2, "X" in J2,

=SUMPRODUCT(($A$2:$A$6=J2)*$B$2:$B$6*$C$2:$C$6)/SUMPRODUCT(($A$2:$A
$6=J2)*$C$2:$C$6)

HTH
Kostis Vezerides

On Feb 1, 8:28 pm, MB51 wrote:
My data table looks something like this:
Col A Col B Col C
Product Score Stops
X 98% 100
Y 97% 50
X 75% 150
Z 80% 25
X 99% 200

Totals:
X ?? 450 (using SUMIF for X)
Y ?? 50
Z ?? 25

I would like to get the weighted average by product (such as x) of column B
(score) based on column C (the weight of each row's score based on its % of
X's total stops).

The ?? in Totals should return the sum of
((98%*(100/450))+(75%*(150/450))+(99%*(200/450))) but I am looking for a more
versatile way to do this so when I add products/results in the future it will
include them in the weighted average.

Weighted average using SUMIF and/or SUMPRODUCT
 

Thank you...it worked!

"vezerid" wrote:

With ?? in K2, "X" in J2,

=SUMPRODUCT(($A$2:$A$6=J2)*$B$2:$B$6*$C$2:$C$6)/SUMPRODUCT(($A$2:$A
$6=J2)*$C$2:$C$6)

HTH
Kostis Vezerides

On Feb 1, 8:28 pm, MB51 wrote:
My data table looks something like this:
Col A Col B Col C
Product Score Stops
X 98% 100
Y 97% 50
X 75% 150
Z 80% 25
X 99% 200

Totals:
X ?? 450 (using SUMIF for X)
Y ?? 50
Z ?? 25

I would like to get the weighted average by product (such as x) of column B
(score) based on column C (the weight of each row's score based on its % of
X's total stops).

The ?? in Totals should return the sum of
((98%*(100/450))+(75%*(150/450))+(99%*(200/450))) but I am looking for a more
versatile way to do this so when I add products/results in the future it will
include them in the weighted average.

Weighted average using SUMIF and/or SUMPRODUCT
 

You are welcome! Thanks for the feedback.



On Feb 1, 9:22 pm, MB51 wrote:
Thank you...it worked!

"vezerid" wrote:
With ?? in K2, "X" in J2,

=SUMPRODUCT(($A$2:$A$6=J2)*$B$2:$B$6*$C$2:$C$6)/SUMPRODUCT(($A$2:$A
$6=J2)*$C$2:$C$6)

HTH
Kostis Vezerides

On Feb 1, 8:28 pm, MB51 wrote:
My data table looks something like this:
Col A Col B Col C
Product Score Stops
X 98% 100
Y 97% 50
X 75% 150
Z 80% 25
X 99% 200

Totals:
X ?? 450 (using SUMIF for X)
Y ?? 50
Z ?? 25

I would like to get the weighted average by product (such as x) of column B
(score) based on column C (the weight of each row's score based on its % of
X's total stops).

The ?? in Totals should return the sum of
((98%*(100/450))+(75%*(150/450))+(99%*(200/450))) but I am looking for a more
versatile way to do this so when I add products/results in the future it will
include them in the weighted average.