sumproduct formula for horizontal data

Hi,

I have data arranged horizontally, like this

Column A / B / C / D
Row 1 Channel1
Row2 Sales / Supplier Price / Return % / Margin %
Row3 values
And then the same columns repeat for channel 2

I want to calculate a weighted average for all channels for the return %. I would need a formula multiplying the sales columns with the return% columns, summing up the values and dividing by the sum of the sales. I tried sumproduct but I don't get the correct result. Any ideas how to make it work?

Thanks!

Jules Pop, Good morning.

Please, give us some example about the data you have and what results you expect to receive.

You can attach too your excel file to easier things for who wants to help you.
To attach click GO ADVANCED buttom on edit window and click MANAGE ATTACHMENTS button.

I believe a lot of persons will help you.

=SUMPRODUCT((B$2:J$2=N$2)*(B3:J3)*(D$2:L$2=P$2)*(D3:L3))/N3
Try this formula and copy towards down

it will not work if select entire row array, why because sales starts at Column B and ends at Column J, where as Returns starts at Column D and ends at Column L
So for Sales (B$2:J$2=N$2)*(B3:J3) and for Returns (D$2:L$2=P$2)*(D3:L3) was taken in the formula
if you =SUMPRODUCT((B$2:L$2=N$2)*(B3:L3)*(B$2:L$2=P$2)*(B3:L3))/N3 used this formula the answer would be "0"

Originally Posted by nflsales

=SUMPRODUCT((B$2:J$2=N$2)*(B3:J3)*(D$2:L$2=P$2)*(D3:L3))/N3

You can actually get the same result with one of the conditions removed, e.g.

=SUMPRODUCT((B$2:J$2=N$2)*(B3:J3)*(D3:L3))/N3