Re-Calc from new cell if so many cells are blank

I have 2 columns.

Column E has the amount of time each day.
Column G is where I want the Sum to be displayed.

I need Column G to do a =Sum(E4:E33), but if anywhere in between E4 and E33 there is a period of 5 days where it totals 0 then I want column G to re-add from the first day where there is a amount in it.

Ex: E4 to E10 totals 7 (1 per day), from E11 to E15 totals 0, from E16 to E33 totals 18 (1 per day again).

In Column G at G10 it should total 7, at G11 it should be 6, G12 5 etc until it hits G15 where it sees the 5 previous days nothing was entered so it enters a value of 0, because G15 is set to 0, G16 will start adding from E16 till E33, until somewhere down the line it sees another period of 5 days where it adds up to 0 again.

What happens if there are more or less than 5 consecutive 0's?

What about if there are less than 5 0's?

This is what I've got so far and it accounts for any number of 0's, not necessarily 5.

In G4,

=IF(ISNUMBER(MATCH(0,$E4:$E$33,0)-1),MATCH(0,$E4:$E$33,0)-1,COUNTIF(E4:$E$33,1)) copied down

Does it work for you?

If you post a sample sheet showing a sample listing and expected results, then it may be easire to get a working solution.

Please ensure that the sample shows what will happen in different cases (ie. varying number of continuous 0's).

Can you try again and upload an .XLS spreadsheet instead of a .jpg

In H33:

If you are entering 0's in column E, then....

Please Login or Register  to view this content.

If you are entering blanks for 0's in column H, then....

Please Login or Register  to view this content.

Either formula must be confirmed with CTRL+SHIFT+ENTER, not just ENTER and then copied down.

Originally Posted by zny_kan

Wow, seems to work perfect.

Why did you put in $E$3 instead of $E$4, does it make a difference?

It doesn't make a difference. I just picked the wrong start of the range...

That Match() formulation will find the last entry that matches the condition in the denominator of the Lookup Array argument (ie. the 2nd argument of the Match function).

If you select the cell with the formula and go to Tools|Formula Auditing|Evaluate Formula and click Evaluate to step through the way the formula is evaluated, you will see what is going on.

The denominator, when evaluated, creates an array of TRUEs and FALSEs depending on if the condition is met or not in each element of the array. The 1/denominator will then create an array of 1's or #DIV/0 errros because 1/TRUE is the same as 1/1 which =1 and 1/FALSE is the same as 1/0 which results in an error....

Now the next thing for the Match() function to do is look for a 2 in that resultant array... It can't find one, but since the last (optional) argument of the Match function is left out, the function looks for the last entry in the array that is smaller than or equal to the 2.. ie. the last 1 in the array is targeted and that postion is returned... then the Index() part takes over and extracts the corresponding value.

Hope that helps.