Subtotaling Sequential 0's

ktpalmer · #1 06-20-2010, 06:16 PM

Hi everyone!

I need help for a formula to subtotal a set of zeros in the column next to it.
e.g. A1,A2,A3 all = 0, and A4 = 1, then in B3 will be the subtotal 3.
Have attached an example of what I need the spreadsheet to look like.

The formula is going into a spreadsheet that has over 8000 cells in a column, representing every 10 seconds of the day.

Is this possible?

Many thanks, Katie.

ConneXionLost · #2 06-20-2010, 09:06 PM

Hi Katie,

Give this a try.

Cheers,

Cpat · #3 06-23-2010, 06:54 AM

I have a 2 column solution, the left column builds a running total and the right containing the sums of all the 0's.

The left column contains:
=IF(P6=0,IF(Q5>0,Q5+1,1),0)
for all rows.
the right column contains:
=IF(Q6>0,IF(Q7=0,Q6,""),"")
for all rows.
Then to get a total count in all the rows, sum the right column.
Give this a try