Excel Sumproduct to calculate Amount over Hurdle per person

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,677
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.

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


<!-- ######### 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:

Some videos you may like

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
Joined
May 18, 2009
Messages
1,677
Posting Criteria again so it visible.

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


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


Sheet1

LMNO
1Sales AgentHurdleAmountDesired Solution
2Apple$500,000 $581,822 $438,614
3B- $652,108
4C$900,000 $7,722
5D$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>

Spreadsheet Formulas
CellFormula
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
Joined
May 18, 2009
Messages
1,677
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
<html><head><title>Excel Jeanie HTML</title></head>******>


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


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


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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Try to explain how $438,614 obtains for Apple with hurdle = $500,000 without reference to any formula.
 

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,677
Hi Aladin,
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Hi Aladin,
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 .
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,516
Members
409,583
Latest member
gkarthick

This Week's Hot Topics

Top