Reset SUM at the end of the month

[mixers99]

Hi all.

Is there a way to reset sum at the end of the month? I have 3 columns: date, quantity for the date and total. I'd like the total to reset at the end of the month and start over. Please take a look at the attachement.Running total resets.xlsx

Thanks.

[Pete_UK]

You could use this formula in D2:

=SUM(C2,IF(COUNTIF(A$2:A2,LEFT(A2,8)&"*")=1,0,D1))

Copy down as required.

Note that your dates are text values for me, as . is not recognised as a date separator in my regional settings.

Note also that you may need to use semicolons ( ; ) in the formula rather than commas ( , ).

Hope this helps.

Pete

[mixers99]

Thanks for your reply, Pete.

Can you breakdown your formula? Where do I input begginning and the end of the month?

Thanks.

[mixers99]

What do I do if I need to change the reset at the end of the day?

[Pete_UK]

The year and month is derived by the expression:

LEFT(A2,8)

within the COUNTIF function. This is then compared with the range A$2:A2 - note that this is a variable range which changes as the formula ic copied down, to A$2:A3, A$2:A4, and so on. If the COUNTIF function results in 1, then this (row) must be the first one in which that year and month occur, and so zero will be added on to the value in column C, but for all other dates the cumulative value in column D will be added to the value from column C, and thus this reset the sum at the start of each month.

Hope this helps.

Pete

[Pete_UK]

What do I do if I need to change the reset at the end of the day?

In that case you don't need to have the LEFT function - you can do this instead:

=SUM(C2,IF(COUNTIF(A$2:A2,A2)=1,0,D1))

Hope this helps.

Pete

[mixers99]

In that case you don't need to have the LEFT function - you can do this instead:

=SUM(C2,IF(COUNTIF(A$2:A2,A2)=1,0,D1))

Hope this helps.

Pete

Wow, this is exactly what I needed. Thank you so much. If I could pick your brain a little more: what if I wanted to remove first 4 rows from calculation in each day? Is that possible?

Thanks again!

[Pete_UK]

You could use this formula in E2:

=SUM(IF(COUNTIF(A$2:A2,A2)<=4,0,C2),IF(COUNTIF(A$2:A2,A2)<=4,0,E1))

Note that I put it in column E for direct comparison with column D. If you want to put it in D2 you will need to change the E1 (shown in red) to D1.

Hope this helps.

Pete

[mixers99]

Thank you so much for your help,Pete!

[Pete_UK]

Glad to help, and thanks for marking the thread as Solved.

In addition, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete