# SUMPRODUCT Help

#### Banker1

##### Active Member
I was helping another member with SUMPRODUCT, he was trying to calc weighted averages given a specific data set with a few different variables (see the HTML below).
Sumproduct_Solution.xls
ABCDEFGHI
1Names% CodeEarningsPercentCombined
2MikeA3,300100%375
3DaveB12,600100%1,650
4BillC3,900100%650
5CarlD3,50090%405
6
7
8MikeDaveBillCarlABCD
910020030040025%0%0%10%
1050060070080010%20%0%10%
119001,00080060010%20%30%10%
126007,3002001005%10%20%10%
1350030020010025%0%0%20%
142002,00030010010%20%0%5%
1520080090080010%20%30%20%
163004005006005%10%20%5%
173,30012,6003,9003,500100%100%100%90%
18----0%0%0%0%
Sheet1

The SUMPRODUCT formula (highlighted in yellow) works as long as you see it, if I change any of the variables (Names and % Code columns) the result goes to 0. For example, Dave with a D % code results in 0 and should be 1,250. In looking at the arrays SUMPRODUCT creates I noticed the 1's & 0's don't line up which I think is causing the issue, unfortunately for me I can't figure out how to get it to work...

Any ideas?

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### RoryA

##### MrExcel MVP, Moderator
I would use this in E2:
Code:
``=SUMPRODUCT(INDEX(\$A\$9:\$D\$16,,MATCH(\$A2,\$A\$8:\$D\$8,0))*INDEX(\$F\$9:\$I\$16,,MATCH(\$B2,\$F\$8:\$I\$8,0)))``

#### Banker1

##### Active Member
Thank you sir, I was trying to get to that but forgot the ",," in the Match formula (doh!). Must have a case of the Mondays...

Replies
2
Views
381
Replies
9
Views
386
Replies
0
Views
249
Replies
0
Views
215
Replies
3
Views
246

1,190,795
Messages
5,982,968
Members
439,808
Latest member
agutosay

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back