Need a formula for running total to reset at zero

[jconnelley]

I need help with a formula to keep a running total from each month unless a "zero" occurs. Then it would reset and begin counting over again.
I have attached a sample file.

[bebo021999]

Which cells are your desired output and how to calculate?

[jconnelley]

The desired output would be in column C.

For example, A. Green on line 2, current has a "0" because he has no duty scheduled in April. That's why the current total is "0" in C2. Hope I am answering correctly. I appreciate your help!

[bebo021999]

How we can know April is used? Is it the previous month of current month (May)?

[jconnelley]

This was done up until April. We could enter a physical "0" if that makes things work better. I am very open to rearranging this etc...

[ChemistB]

First I set up a defined name.
With the active cell in row 2, Go to Name Manager and
CurrRng = =Officer!$D2:INDEX(Officer!$D2:$N2,MATCH(TEXT(EOMONTH(TODAY(),-1),"mmm"),Officer!$D$1:$N$1,0))

Then you'll need Arrayed Formulas
In C2 copied down
=IFERROR(SUM(INDEX(CurrRng,MATCH(2,1/ISBLANK(CurrRng))):$F2),SUM($D2:INDEX($D2:$N2,MATCH(TEXT(EOMONTH(TODAY(),-1),"mmm"),$D$1:$N$1,0))))

For arrayed formulas
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[jconnelley]

Thanks so much for your response as well ChemistB. However, it doesn't appear to work correctly. If testing it and entering a "0" in cell F2 (April), the total should change to a "0". If there ever is a 0, the total should start over at zero and start counting over again.

[ChemistB]

Yes, my formula works on blanks, not zeros.

[ChemistB]

Here is the formula modified for 0 instead of blanks

[jconnelley]

It looks like it works great until it encounters a zero because it doesn't start over again with the next month unless you enter the formula and create an array. Do you need to do that every month? Otherwise, it appears very close to working.
I want to try and get it to work without having to update the formula monthly though. Unless I am missing something, when you go to enter a number in the column for May (G2) it will only start over if I update the formula and press "Ctrl,Shift,Enter" and then it appears to work. So is that how I would use it each month? Sorry for the questions, but its probably user error on my end. Thanks for your patience and help.

[ChemistB]

The formula is set up (hopefully) to work from the previous month so since it's May, it works through April. On June 1, it will work through May. That's the whole defined name TEXT(EOMONTH(TODAY(),-1),"mmm") part. I assumed you would put in numbers at the beginning of the next month. So, unless you change your computer clock, it's hard to test.

[jconnelley]

Oh, I see! Ok. Thanks so much!