Reset SUM at the end of the month

Thanks for your reply, Pete.

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

Thanks.

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

Originally Posted by mixers99

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

Originally Posted by Pete_UK

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!

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

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