Running total formula question

[excelsishya]

Hi all,

I am need of running total formula help.

Running total

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 121px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td> <td>N</td> <td>O</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>
</td> <td>Jan</td> <td>Feb</td> <td>Mar</td> <td>Apr</td> <td>May</td> <td>Jun</td> <td>Jul</td> <td>Aug</td> <td>Sep</td> <td>Oct</td> <td>Nov</td> <td>Dec</td> <td>Jan</td> <td>Feb</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="font-weight: bold;">Net profit plug</td> <td style="font-weight: bold; text-align: right;">1</td> <td style="font-weight: bold; text-align: right;">2</td> <td style="font-weight: bold; text-align: right;">3</td> <td style="font-weight: bold; text-align: right;">4</td> <td style="font-weight: bold; text-align: right;">5</td> <td style="font-weight: bold; text-align: right;">6</td> <td style="font-weight: bold; text-align: right;">7</td> <td style="font-weight: bold; text-align: right;">8</td> <td style="font-weight: bold; text-align: right;">9</td> <td style="font-weight: bold; text-align: right;">10</td> <td style="font-weight: bold; text-align: right;">11</td> <td style="font-weight: bold; text-align: right;">12</td> <td style="font-weight: bold; text-align: right;">1</td> <td style="font-weight: bold; text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>Np</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr></tbody></table>
Row 4 is result

at b4 i am using =SUM($B6:B6)

at n4 i am using =SUM($N6:N6)

i am looking for 1 dynamic formula where when month is new it restarts running total formula.

Will it possible to give 2 formula one using date as trigger ie at each jan running total starts and another where count 12 is trigger to start running total .

Thanks

 

[Peter_SSs]

In B4, copied across, try

=B6+IF(B3="Jan",0,A4)

 

[excelsishya]

Peter thanks,formula works.

based on your logic i was also able to get answer for 2nd version of formula for my problem also"another where count 12 is trigger to start running total"

i used =B6+IF(MOD(COLUMN(A1),12)=1,0,A4)

 

[Peter_SSs]

Peter thanks,formula works.

based on your logic i was also able to get answer for 2nd version of formula for my problem also"another where count 12 is trigger to start running total"

i used =B6+IF(MOD(COLUMN(A1),12)=1,0,A4)

Good adaptation but it isn't very robust. Suppose that you decide you need a new column A and insert it to the left of your current data, your formula would return incorrect results (try it).

I would suggest this slight variation:
=B6+IF(MOD(COLUMNS($B6:B6),12)=1,0,A4)

 

[excelsishya]

Thanks again,

I changed column to columns in formula.