# Excel Sumproduct to calculate Amount over Hurdle per person

#### Biz

##### Well-known Member
Dear All,

I'm trying to calculate amount over the criteria, as per range ("L1:M5").
The desired solution in Col O per person. Created Helper Column in Col J but it's limited to
one Sales Agent i.e Apple.

I was wondering, if the calculation in col J was possible, without a Helper Column in Col J.
My formula in cell N2 is not generating the correct result.

<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
ABCDEFGJKLMNO
1DateSales AgentValueContract StatusCumulative Running TotalCum Settlement Running totalPrior Cumulative Running TotalHelper ColumnSales AgentHurdleAmountDesired Solution
26/10/17Apple\$240,681Settlement\$240,681\$240,681--\$240,681Apple\$500,000\$581,822\$438,614
38/10/17B\$243,173Qualified\$243,173----B-\$652,108
410/10/17C\$201,350Settlement\$201,350\$201,350--\$201,350C\$900,000\$7,722
512/10/17D\$259,721Settlement\$259,721\$259,721--\$259,721D\$750,000\$48,808
614/10/17Apple\$116,111Qualified\$356,792\$240,681\$240,681-\$240,681
716/10/17B\$120,828Settlement\$364,001\$120,828--\$120,828
818/10/17C\$207,966Settlement\$409,316\$409,316\$201,350-\$409,316
920/10/17D\$273,373Settlement\$533,094\$533,094\$259,721-\$533,094
1022/10/17Apple\$126,762\$356,792\$240,681\$240,681-\$240,681
1124/10/17B\$133,826Settlement\$497,827\$254,654\$120,828-\$254,654
1226/10/17C\$186,833Settlement\$596,149\$596,149\$409,316-\$596,149
1328/10/17D\$265,714Settlement\$798,808\$798,808\$533,094-\$798,808
1430/10/17Apple\$152,705Settlement\$509,497\$393,386\$240,681\$9,497\$152,705
151/11/17B\$206,310Settlement\$704,137\$460,964\$254,654-\$460,964
163/11/17C\$120,429Settlement\$716,578\$716,578\$596,149-\$716,578
175/11/17Apple\$205,981Settlement\$715,478\$599,367\$393,386#######\$205,981
187/11/17C\$191,144Settlement\$907,722\$907,722\$716,578-\$907,722
199/11/17b\$191,144Settlement\$895,281\$652,108\$460,964-\$652,108
2011/11/17C\$191,144Qualified\$1,098,866\$907,722\$907,722-\$907,722
219/11/17B\$162,691Exchanged\$895,281\$652,108\$652,108-\$652,108
2211/11/17Apple\$274,247Qualified\$989,725\$599,367\$599,367-\$599,367
2313/11/17Apple\$223,136Settlement\$1,212,861\$822,503\$599,367#######\$497,383
Sheet1

<!-- ######### End Created Html Code To Copy ########## -->

</body></html>

Your help would be greatly appreciated. Hoping for a formula solution without a helper column. I believe sumproducts maybe be useful to desire the solution based on multiple criterias.

Kind Regards

Biz

Last edited:

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Biz

##### Well-known Member
Posting Criteria again so it visible.

<title>Excel Jeanie HTML</title>******>

<!-- ######### Start Created Html Code To Copy ########## -->

Sheet1

 L M N O 1 Sales Agent Hurdle Amount Desired Solution 2 Apple \$500,000 \$581,822 \$438,614 3 B - \$652,108 4 C \$900,000 \$7,722 5 D \$750,000 \$48,808 6 7 8 9 10

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:90px;"><col style="width:90px;"><col style="width:90px;"><col style="width:81px;"></colgroup><tbody>
</tbody>

 Cell Formula M2 =500000 N2 =SUMPRODUCT(--(\$B\$2:\$B23=\$L\$2),--(\$D\$2:\$D23="Settlement"),--(\$E\$2:\$E23>=M2),\$F\$2:\$F23-\$G\$2:\$G23)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

<!-- ######### End Created Html Code To Copy ########## -->

#### Biz

##### Well-known Member
Hi Guys,
I had bit of progress and I'm getting my desired solution. Is it possible to eliminate the need of helper column H.

Summary Worksheet

<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
ABCDE
1Sales AgentHurdle AmountAmountDesired Solution
2Apple\$500,000\$438,614\$438,614TRUE
3B-\$652,108\$652,108TRUE
4C\$900,000\$7,722\$7,722TRUE
5D\$750,000\$48,808\$48,808TRUE
Summary

?

?

<!-- ######### End Created Html Code To Copy ########## -->

?

</body></html>

<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
ABCDEFGH
1DateSales AgentValueContract StatusCumulative Running TotalCum Settlement Running totalHurdle AmountHelper Column
26/10/17Apple\$240,681Settlement\$240,681\$240,681500,000-
38/10/17B\$243,173Qualified\$243,173-0-
410/10/17C\$201,350Settlement\$201,350\$201,350900,000-
512/10/17D\$259,721Settlement\$259,721\$259,721750,000-
614/10/17Apple\$116,111Qualified\$356,792\$240,681500,000-
716/10/17B\$120,828Settlement\$364,001\$120,8280\$120,828
818/10/17C\$207,966Settlement\$409,316\$409,316900,000-
920/10/17D\$273,373Settlement\$533,094\$533,094750,000-
1022/10/17Apple\$126,762\$356,792\$240,681500,000-
1124/10/17B\$133,826Settlement\$497,827\$254,6540\$133,826
1226/10/17C\$186,833Settlement\$596,149\$596,149900,000-
1328/10/17D\$265,714Settlement\$798,808\$798,808750,000\$48,808
1430/10/17Apple\$152,705Settlement\$509,497\$393,386500,000\$9,497
151/11/17B\$206,310Settlement\$704,137\$460,9640\$206,310
163/11/17C\$120,429Settlement\$716,578\$716,578900,000-
175/11/17Apple\$205,981Settlement\$715,478\$599,367500,000\$205,981
187/11/17C\$191,144Settlement\$907,722\$907,722900,000\$7,722
199/11/17b\$191,144Settlement\$895,281\$652,1080\$191,144
2011/11/17C\$191,144Qualified\$1,098,866\$907,722900,000-
219/11/17B\$162,691Exchanged\$895,281\$652,1080-
2211/11/17Apple\$274,247Qualified\$989,725\$599,367500,000-
2313/11/17Apple\$223,136Settlement\$1,212,861\$822,503500,000\$223,136
Cals

?

?

<!-- ######### End Created Html Code To Copy ########## -->

?

</body></html>

Your help would be greatly appreciated.

Kind Regards

Biz

##### MrExcel MVP
Try to explain how \$438,614 obtains for Apple with hurdle = \$500,000 without reference to any formula.

#### Biz

##### Well-known Member
I have done calculations in the "cals" worksheet tab, col H which calculates the excess amount over the hurdle per person.
Cell H2
=IF(D2="Settlement",MIN(SUMPRODUCT(--(E2>G2),(E2-G2)),C2),0)

The "Summary" worksheet tab in cell C2, adds up cells in Col H in "Cals" worksheet tab which exceeds hurdle amount using "Cumulative Running Total" col E.

Does it make sense?

Kind Regards

Biz

##### MrExcel MVP
I have done calculations in the "cals" worksheet tab, col H which calculates the excess amount over the hurdle per person.
Cell H2
=IF(D2="Settlement",MIN(SUMPRODUCT(--(E2>G2),(E2-G2)),C2),0)

The "Summary" worksheet tab in cell C2, adds up cells in Col H in "Cals" worksheet tab which exceeds hurdle amount using "Cumulative Running Total" col E.

Does it make sense?

Kind Regards

Biz

This is not I asked for. If this is the solution, ignore my post, otherwise see post #4 .