# Reset Running Total

#### davin1130

##### New Member
I’m really struggling with this one … I have a spread sheet and Row 1 columns A-L contain the months. Row 2, I will be inputting numeric values for those months. Row 3 column A, I want to keep a running total for the values in row 2, however, the value will need to reset if the running total exceeds 100. The reset should begin with the next months value. So, if i exceed 100 in July, the reset value should begin with the value for August. Then in row 3 column B I want to put the month that the “reset” occurs from row 3 column A. Can anyone help. I know that this is similar to a previous post i put out, but I was given new direction for this one.

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

#### GraH

##### Well-known Member
Since you start in column A it is difficult to make a formula that looks at previous result. You may try:
[A3]= A2
[B3] = IF(A3>100,B2,A3+B2) and drag across

Book1
ABCDEFGHIJKLMN
1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2393554636772232311405366
33974128631307295118115110466
4
5JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
6574086776980508865218865
757971837714680130881532110965
8
Sheet1
Cell Formulas
RangeFormula
A3A3=A2
B3:L3,B7:M7B3=IF(A3>100,B2,A3+B2)

#### davin1130

##### New Member
Thank you so much for your reply ... however i'm looking to only have one cell with the sum total (B3) .... if i enter values in the month row ... only cell B3 would change ... and when that value exceeds 100 ... B3 will start all over again.

#### davin1130

##### New Member
i'm ok with shifting the columns to make it work.

#### davin1130

##### New Member

You nailed it for the most part .... would only like to have one sum total cell

#### GraH

##### Well-known Member
Can you upload sample data with expected result, using XL2BB?
I do not get your comment on one cell total.

#### GraH

##### Well-known Member
Hmm, still under the reserve I understand what you want to do, if it's only about a visual effect, you can accomplish it with a custom format: [<100]"",0
Book1
ABCDEFGHIJKLM
1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2393554636772232311405366
3  128 130  118  104
4
5JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
6574086776980508865218865
7  183 146 130 153 109
Sheet1
Cell Formulas
RangeFormula
A3A3=A2
B3:L3,B7:M7B3=IF(A3>100,B2,A3+B2)

Replies
38
Views
999
Replies
1
Views
62
Replies
2
Views
124
Replies
1
Views
56
Replies
6
Views
324

### Forum statistics

1,127,588
Messages
5,625,668
Members
416,125
Latest member
NeedExcelHelp2021

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

### Which adblocker are you using?

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

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