Combine SumProduct with SumIfs

[brems]

Hi,


I want to calculate the weighted average pricing of each category and therefore I use SumProduct but instead of selecting the cells for each category I want to use SumIfs together with SumProduct to do it for me. It would be more correct and much quicker.


Below is an example of the data.


Row 1 Row 2 Row 3

Product Category Amount

Product 1 cat 1 50

Product 4 cat 1 8

Product 5 cat 1 4

Product 2 cat 2 10

Product 3 cat 2 36

Product 6 cat 3 42

Total 150


Pricing

Product 1 cat 1 20,00 €

Product 4 cat 1 60,00 €

Product 5 cat 1 90,00 €

Product 2 cat 2 150,00 €

Product 3 cat 2 5,00 €

Product 6 cat 3 7,00 €


weighted avg cat 1 29,68 € =SOMPRODUCT(C3:C5;C12:C14)/SOM(C3:C5)

weighted avg cat 2 36,52 € =SOMPRODUCT(C6:C7;C15:C16)/SOM(C6:C7)

weighted avg cat 3 6,00 € =SOMPRODUCT(C8)/C17


Can you please assist in finding the right way of working?

 

[Hui]

Brems


Firstly, Welcome to the Chandoo.org forums.


Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[brems]

Hi Hui,


Thanks for your welcome. I posted the sample sheet on google docs and have set the properties to shared.


https://docs.google.com/spreadsheet/ccc?key=0AkUCb6qKhWrZdEVhRnJ0d1NzcHN3aEd5Y3JjYzl3Tmc

 

[Hui]

Brems


in C19:

=SUMPRODUCT(($B$3:$B$8=B19)*$C$3:$C$8,($B$12:$B$17=B19)*$C$12:$C$17)/SUMIFS($C$3:$C$8,$B$3:$B$8,B19)

Copy down


By the way the value in C21 is wrong it should be 7

 

[brems]

Hi Hui,


Works perfect! Thanks a lot.

Learned something from it. ;-)