# Restart running total after X number of rows

#### nielf

##### Board Regular
I'm trying to come up with a formula that can 'restart' a running total after 12 rows.

Currently my data looks like this where the running total is calculated using the formula in C2 is
Code:
``=SUM(\$B\$2:B2)``

This works fine until I reach row 14. Here I would like the formula to 'restart' so it would be
Code:
``=SUM(\$B\$14:B14)``
.

Is there anyway I can achive this 'restart' by counting the number of rows so I don't have to manually update the formula as I go?

I know it is possible to use the function in a PivotTable but that's not the solution I'm looking for.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### gallen

##### Well-known Member
This line is pointless and will basically copy what is in cell B2.

Code:
``=SUM(\$B\$2:B2)``

you could just as easily have
Code:
``=B2``

I think it must be a typo?

#### footoo

##### Well-known Member
=if(mod(row(),12)-2=0,b2,b2+c1)

#### nielf

##### Board Regular
@gallen,

Yes, that is the case for the first row. However, I'm looking for the running total so I need that formula for all other rows. E.g. C3 would be

Code:
``=SUM(\$B\$2:B3)``
and not just
Code:
``=B3``
.

#### nielf

##### Board Regular
=if(mod(row(),12)-2=0,b2,b2+c1)

Thank you. That was exactly was I was looking for.

If you have time - could you perhaps explain how the formula works?

Last edited:

Replies
0
Views
371
Replies
12
Views
182
Replies
3
Views
35
Replies
41
Views
436
Replies
5
Views
182

1,191,187
Messages
5,985,194
Members
439,947
Latest member
fabiannic

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