Auto sum numbers based on previous number and a given number

D

daddydool

New Member
Joined
May 7, 2018
Messages
2
Hi,
I have a delivery company and want to create an incentive tracking sheet. My drivers will start accumulating fixed commissions after a certain number of deliveries (below chart);
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD]Driver Incentive # of Orders[/TD]
[TD]Commission per order[/TD]
[TD]Payout[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]AED1.00[/TD]
[TD="align: right"]AED1.00[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]AED2.00[/TD]
[TD="align: right"]AED3.00[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]AED3.00[/TD]
[TD="align: right"]AED6.00[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]AED4.00[/TD]
[TD="align: right"]AED10.00[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]AED5.00[/TD]
[TD="align: right"]AED15.00[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]AED6.00[/TD]
[TD="align: right"]AED21.00[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"]AED7.00[/TD]
[TD="align: right"]AED28.00[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"]AED8.00[/TD]
[TD="align: right"]AED36.00[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"]AED9.00[/TD]
[TD="align: right"]AED45.00[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: right"]AED10.00[/TD]
[TD="align: right"]AED55.00[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: right"]AED10.00[/TD]
[TD="align: right"]AED65.00[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"]AED10.00[/TD]
[TD="align: right"]AED75.00[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: right"]AED10.00[/TD]
[TD="align: right"]AED85.00[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: right"]AED10.00[/TD]
[TD="align: right"]AED95.00[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: right"]AED10.00[/TD]
[TD="align: right"]AED105.00[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: right"]AED10.00[/TD]
[TD="align: right"]AED115.00[/TD]
[/TR]
</tbody>[/TABLE]

What I would like to do is at the closing of the day when entering the total deliveries the cumulative amount to populate in the incentive column of my sheet.
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4CCCC]#F4CCCC[/URL] , align: center"]Driver[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4CCCC]#F4CCCC[/URL] , align: center"]Driver ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4CCCC]#F4CCCC[/URL] , align: center"]Location[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4CCCC]#F4CCCC[/URL] , align: center"]Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4CCCC]#F4CCCC[/URL] , align: center"]Total Deliveries[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4CCCC]#F4CCCC[/URL] , align: center"]Eligibale for incentive[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4CCCC]#F4CCCC[/URL] , align: center"]Driver Incentive[/TD]
[/TR]
[TR]
[TD="align: center"]Nadir Rafique[/TD]
[TD="align: center"]1096[/TD]
[TD="align: center"]Dubai[/TD]
[TD="align: center"]01/05/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E8DFBE]#E8DFBE[/URL] , align: center"]22[/TD]
[TD]yes[/TD]
[TD]85[/TD]
[/TR]
</tbody>[/TABLE]

when entering the amount of 22 or any other total in the total deliveries I would like to have driver incentive auto calculate based on the cumulative incentives in the payout info.
 

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.
Sort by date Sort by votes
Hi,

Based on your description:


Book1
ABCDEFG
1Driver Incentive # of OrdersCommission per orderPayout
210AED1.00AED1.00
311AED2.00AED3.00
412AED3.00AED6.00
513AED4.00AED10.00
614AED5.00AED15.00
715AED6.00AED21.00
816AED7.00AED28.00
917AED8.00AED36.00
1018AED9.00AED45.00
1119AED10.00AED55.00
1220AED10.00AED65.00
1321AED10.00AED75.00
1422AED10.00AED85.00
1523AED10.00AED95.00
1624AED10.00AED105.00
1725AED10.00AED115.00
18
19
20DriverDriver IDLocationDateTotal DeliveriesEligibale for incentiveDriver Incentive
21Nadir Rafique1096Dubai1/5/201822yesAED85.00
22Nadir Rafique1096Dubai1/5/201822yes85
Sheet42
Cell Formulas
RangeFormula
G21=IFERROR(VLOOKUP(E21,A2:C17,3,0),0)
G22=IFERROR(MID(VLOOKUP(E21,A2:C17,3,0),4,15)+0,0)


Use G21 formula if you want to keep AED in front of result.
Use G22 formula if you just want the number.
 
Upvote 0
Hi jtakw,
Thank you so much.

jtakw said:
Hi,

Based on your description:

ABCDEFG
Driver Incentive # of OrdersCommission per orderPayout
AED1.00AED1.00
AED2.00AED3.00
AED3.00AED6.00
AED4.00AED10.00
AED5.00AED15.00
AED6.00AED21.00
AED7.00AED28.00
AED8.00AED36.00
AED9.00AED45.00
AED10.00AED55.00
AED10.00AED65.00
AED10.00AED75.00
AED10.00AED85.00
AED10.00AED95.00
AED10.00AED105.00
AED10.00AED115.00
DriverDriver IDLocationDateTotal DeliveriesEligibale for incentiveDriver Incentive
Nadir RafiqueDubaiyesAED85.00
Nadir RafiqueDubaiyes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]
[TD="align: right"]10[/TD]

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

[TD="align: center"]3[/TD]
[TD="align: right"]11[/TD]

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

[TD="align: center"]4[/TD]
[TD="align: right"]12[/TD]

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

[TD="align: center"]5[/TD]
[TD="align: right"]13[/TD]

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

[TD="align: center"]6[/TD]
[TD="align: right"]14[/TD]

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

[TD="align: center"]7[/TD]
[TD="align: right"]15[/TD]

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

[TD="align: center"]8[/TD]
[TD="align: right"]16[/TD]

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

[TD="align: center"]9[/TD]
[TD="align: right"]17[/TD]

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

[TD="align: center"]10[/TD]
[TD="align: right"]18[/TD]

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

[TD="align: center"]11[/TD]
[TD="align: right"]19[/TD]

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

[TD="align: center"]12[/TD]
[TD="align: right"]20[/TD]

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

[TD="align: center"]13[/TD]
[TD="align: right"]21[/TD]

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

[TD="align: center"]14[/TD]
[TD="align: right"]22[/TD]

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

[TD="align: center"]15[/TD]
[TD="align: right"]23[/TD]

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

[TD="align: center"]16[/TD]
[TD="align: right"]24[/TD]

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

[TD="align: center"]17[/TD]
[TD="align: right"]25[/TD]

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

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

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

[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]22[/TD]

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

[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]22[/TD]

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

</tbody>
Sheet42​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G21[/TH]
[TD="align: left"]=IFERROR(VLOOKUP(E21,A2:C17,3,0),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G22[/TH]
[TD="align: left"]=IFERROR(MID(VLOOKUP(E21,A2:C17,3,0),4,15)+0,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Use G21 formula if you want to keep AED in front of result.
Use G22 formula if you just want the number.
Click to expand...
 
Upvote 0
You're welcome, welcome to the forum.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
10% Time Improvements
Replies
2
Views
733
colinshu
C
R
  • Question Question
Auto Import Specified Data from one sheet t another
Replies
0
Views
566
RedPed
R
N
  • Question Question
Sum every nth column for x instances (to get a weekly YTD number)
Replies
8
Views
1K
Nishant94
Nishant94
T
  • Question Question
sum range with Index Match
Replies
5
Views
2K
Aladin Akyurek
Aladin Akyurek
R
  • Question Question
Sum based on Date
Replies
5
Views
686
jtakw
jtakw

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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