# Weighted Average Based On Category data from 3 Columns

Hello EE,

Can someone please help me write a weighted average formula to calculate the weighted average for Sammy Puff?  I need to calculate the weighted average for Online, Fax, Mail  in column c and Sammy Puff in columns A and B

I have attached a sample data.

Thank you.
Book44.xlsx
LVL 1
###### Who is Participating?

Commented:
See sheet
Book44.xlsx
0

use this formula for Cell J3
``````=(SUMPRODUCT((\$C\$2:\$C\$160=J2)*(\$D\$2:\$E\$160)))/SUMIF(\$C\$2:\$C\$160,J2,\$D\$2:\$D\$160)
``````
Cell K3
``````=(SUMPRODUCT((\$C\$2:\$C\$160=K2)*(\$D\$2:\$E\$160)))/SUMIF(\$C\$2:\$C\$160,K2,\$D\$2:\$D\$160)
``````
Cell L3
``````=(SUMPRODUCT((\$C\$2:\$C\$160=L2)*(\$D\$2:\$E\$160)))/SUMIF(\$C\$2:\$C\$160,L2,\$D\$2:\$D\$160)
``````

Thanks
0

Author Commented:
Hi itjockey,

How can i add Sammy Puff in Columns A and B to the formula.  I only need the calculation if the value in column A is Sammy Puff and value in column B is Sammy Puff...
0

i dint find any match where column A = Sammy Puff and column B =Sammy Puff in same row ...that is why i had consider only column B
0

``````=SUMPRODUCT((\$C\$2:\$C\$160=J2)*(\$B\$2:\$B\$160=\$I3)*(\$A\$2:\$A\$160=\$I3)*\$D\$2:\$E\$160)/SUMIFS(\$D\$2:\$D\$160,\$A\$2:\$A\$160,\$I3,\$B\$2:\$B\$160,\$I3,\$C\$2:\$C\$160,J2)
``````
For column J3 and copy across
0

Author Commented:
oh.  It is an OR.  Where A = Sammy Puff OR column B =Sammy Puff
0

sorry change I3 = 'Sammy Puff in your excel
0

Commented:
Formula requires same name in column A and B in the row, but it has to be A or B.
To get the product sum \$D\$2:\$E\$160 must be \$D\$2:\$D\$160*\$E\$2:\$E\$160

With the names in column I, the formula in J3 will be.
``````=(SUMPRODUCT((\$A\$2:\$A\$160=\$I3)*(\$C\$2:\$C\$160=J\$2)*\$D\$2:\$D\$160*\$E\$2:\$E\$160)+SUMPRODUCT((\$B\$2:\$B\$160=\$I3)*(\$C\$2:\$C\$160=J\$2)*\$D\$2:\$D\$160*\$E\$2:\$E\$160))/(SUMPRODUCT((\$A\$2:\$A\$160=\$I3)*(\$C\$2:\$C\$160=J\$2))+SUMPRODUCT((\$B\$2:\$B\$160=\$I3)*(\$C\$2:\$C\$160=J\$2)))
``````

Copy down and across for all names.
See sheet.
Book44.xlsx
1

Author Commented:
Hi Ejgil Hedegaard

I need the percentages to be shown
0

Author Commented:
Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.