Forum Discussion
Solved
SumProduct for Weightd Average
Hello Experts,
I am trying to use SumProduct to return the weighted average of various rates and amounts for companies. How can I use SumProduct to do this? I think I need an If statement in the Array but I dont think my IF statement is correct since I get a #NA. Its not easy to explain. Its is better to view the attached file.
my sumproduct formula (its not correct😞
=SUMPRODUCT(IF(Vendor="Co A",Rate,0),IF(Vendor="Co A",Weight,0))/SUMIF(Vendor,"Co A",EUR)
let me know if you have any questions.
thank you
The definition of the Rate range is not correct - it refers to =Confirmations!$D$34:$D$54 instead of to =Confirmations!$D$7:$D$54. If you change that, the formula will work.
For example:
=SUM(IF(Vendor="Co A",EUR*Weight))/SUMIF(Vendor,"Co A",Weight)
or
=SUMPRODUCT((Vendor="Co A")*EUR*Weight)/SUMIF(Vendor,"Co A",Weight)
for EUR, and
=SUM(IF(Vendor="Co A",Rate*Weight))/SUMIF(Vendor,"Co A",Weight)
or
=SUMPRODUCT((Vendor="Co A")*Rate*Weight)/SUMIF(Vendor,"Co A",Weight)
for Rate.
- Hi Hans, thank you. I tried the 2 formulas for RATE and each of the formulas returns a #N/A. I imagine I am doing something wrong but I am not sure what it could be?
The definition of the Rate range is not correct - it refers to =Confirmations!$D$34:$D$54 instead of to =Confirmations!$D$7:$D$54. If you change that, the formula will work.
