Excel Sumproduct to calculate Amount over Hurdle per person

B

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,773
Office Version
  1. 2021
Platform
  1. Windows
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:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Posting Criteria again so it visible.

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


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


Sheet1

LMNO
Sales AgentHurdle
Apple
B
C
D

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: #ffcc99"]Amount[/TD]
[TD="bgcolor: #339966"]Desired Solution[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]$500,000 [/TD]
[TD="align: right"]$581,822 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ccffff]#ccffff[/URL] , align: right"]$438,614 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]- [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ccffff]#ccffff[/URL] , align: right"]$652,108 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]$900,000 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ccffff]#ccffff[/URL] , align: right"]$7,722 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]$750,000 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ccffff]#ccffff[/URL] , align: right"]$48,808 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

</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 ########## -->
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Biz said:
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
Click to expand...

This is not I asked for. If this is the solution, ignore my post, otherwise see post #4 .
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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?

Disable AdBlock

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
Back
Top