fun2excel4money
New Member
- Joined
- Dec 24, 2016
- Messages
- 18
Hello Community,
Long time lurker, first time poster. I am attempting to make a more concise formula. The situation is as follows:
<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!
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!