A formula to help with a cumulative sum question? (hopefully!)

Hi there

I am using a Macbook. I am currently trying to find out if there is a way, in which, I can "reset a cumulative formula", if a certain criteria is met.

The attached document is showing a sales persons revenue for each month (C13:C36), the cumulative 5 month rolling total (D13:D36), and then also the sales persons monthly revenue target (cell C5), and also a promotion target (cell C6).

If this sales person hits the promotion target, they will receive a promotion, and the promotion date will be entered into cell C7, and their new monthly target and promotion target will be entered in cells C5 & C6 respectively.

So the problem I have, is once someone is promoted, their 5 month cumulative total needs to start from zero again, but am struggling to think of a way I can do this?

Thanks

Please explain the issue and help required in your opening post as well as in your workbook. Please edit your post NOW with the information requested.

Are you using a Mac? If so, then the word 'Mac' also need to be shown next to the Office version in your profile.

See if this formula, pasted into D42 and copied down, will work:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Please try in F42 and copy down:

Formula:

Please Login or Register  to view this content.