Weighted Average with sumif

[DataLytic]

Hi I would like get weighted average margin by category.
I want to use the sales to weight the margins.
Any help? thanking in advance

[TABLE="width: 458"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Invoice Number[/TD]
[TD]Product[/TD]
[TD]Sales[/TD]
[TD]Margin[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]22[/TD]
[TD]Pen[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]15%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]28[/TD]
[TD]Books[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]11%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]26[/TD]
[TD]Rubber[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]25%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]32[/TD]
[TD]Ink[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]65%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]152[/TD]
[TD]Pen[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]48%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]225[/TD]
[TD]Books[/TD]
[TD="align: right"]451[/TD]
[TD="align: right"]54%[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]365[/TD]
[TD]Rubber[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]22%[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]445[/TD]
[TD]Ink[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]15%[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]224[/TD]
[TD]Pen[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]48%[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]257[/TD]
[TD]Books[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]65%[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]45[/TD]
[TD]Rubber[/TD]
[TD="align: right"]889[/TD]
[TD="align: right"]33%[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]6987[/TD]
[TD]Ink[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Weighted Average Margin[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD] [/TD]
[TD]Pen[/TD]
[TD] [/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD] [/TD]
[TD]Books[/TD]
[TD] [/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD] [/TD]
[TD]Rubber[/TD]
[TD] [/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD] [/TD]
[TD]Ink[/TD]
[TD] [/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

 

[Colo87x]

Hi Data

I have recently been faced with a similar task, and this is the formula I used on your data.

=SUMPRODUCT(--($C$3:$C$14="Pen"),$E$3:$E$14,$D$3:$D$14)/SUMIFS($D$3:$D$14,$C$3:$C$14,"Pen")

You can type in the name "pen/Product" or select the cell in the column to the left.

Let me know if this helps?

Thanks

 

[DataLytic]

Brilliant that works!
Thanks heaps