# Condensed-do-all SUMPRODUCT?

#### fun2excel4money

##### New Member
Hello Community,

Long time lurker, first time poster. I am attempting to make a more concise formula. The situation is as follows:

 JOHN JIM JESS MOD-1 MOD-2 MOD-3 MOD-4 MOD-5 ORD .5 .3 .2 ORD ORD AVG ORD AVG AVG .2 .3 .5 10000 20000 30000 40000 50000 CA 35000 30000 35000 CA 10000 0 0 40000 50000 NY 10000 6000 4000 NY 0 20000 0 0 0 VT 26000 21000 23000 VT 0 0 30000 40000 0

<tbody>
</tbody>

In this scenario, I am focusing on B5 where the output is 35000. I am attempting to find A5 in table F5:F7. Once I find it, I want it to sum up the amounts in G5:G7 using a SUMPRODUCT. The extra bone that I want to throw in is that I then want to specifically sum up all items that are ORD and all that are AVG in G2:K2. I want the numbers to then multiply by the person's ORD percentage in B2:D3.

To illustrate with numbers, CA has a total ORD modification of 50000 and a total AVG modification of 50000. As it relates to John, his ORD % is 50% and his AVG % is 20%. His share of ORD and AVG modifications would be 50000 * .5 + 50000 * .2, or 35000.

The formula I am currently using for B5 is:

=SUMPRODUCT((\$F\$5:\$F\$7=\$A5)*(\$G\$2:\$K\$2=\$G\$2)*\$G\$5:\$K\$7)*SUMPRODUCT((\$A\$2:\$A\$3=\$G\$2)*B\$2:B\$3)+SUMPRODUCT((\$F\$5:\$F\$7=\$A5)*(\$G\$2:\$K\$2=\$I\$2)*\$G\$5:\$K\$7)*SUMPRODUCT((\$A\$2:\$A\$3=\$I\$2)*B\$2:B\$3)

I am looking for a formula that might allow me to use one SUMPRODUCT as opposed to the sum of two SUMPRODUCTs. The actual situation I have, I am using five additional SUMPRODUCT, hence my curiosity of the situation. The problem I am having is that each person has his own percentages and is referenced directly above where I want to enter the formula. G2:K2 are used to let me know which modification is subject to which allocation, but not the allocation percentages attributable to the person.

Please let me know if there is anything I can clarify. A virtual eggnog to those who can help! Thank you!

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

##### MrExcel MVP
In B5 enter, copy across to D5, and down:

=SUMPRODUCT(INDEX(\$G\$5:\$K\$7,MATCH(\$A5,\$F\$5:\$F\$7,0),0),SUMIFS(B\$2:B\$3,\$A\$2:\$A\$3,\$G\$2:\$K\$2))

#### fun2excel4money

##### New Member
ABSOLUTELY INCREDIBLE! Thank you, thank you!

Replies
7
Views
431
Replies
3
Views
192
Replies
3
Views
245
Replies
1
Views
509
Replies
3
Views
491

1,190,613
Messages
5,981,928
Members
439,743
Latest member
KatieO

### 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.

### Which adblocker are you using?

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

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