calculating subtotals for sequential blank cells in 1 column

[Katie]

if i had a column of data and i need to group/subtotal any cells containing
zeros or isolated zeros, wat formula do i put in
eg.

0 )
1 )
0 )
0 ) subtotal = 4 (so i know it was a group of four cells)
4
5
1
6
5
0 )
1 )
0 ) subtotal = 3
6
7
8
0 ) subtotal = 1

basically the data shows steps and rests, and we need to know how long the
rest periods are. single steps surrounded by rest (0) need to be counted as
rest intervals.

 

[Tom Hutchins]

Here is one way. Assuming your data starts in A1...

In B1 enter =--(A1=0)
In B2 enter =IF(A2=0,B1+1,IF(AND(A1=0,A3=0),B1+1,0))
Copy B2 down through all rows of data.

In C1 enter =IF(AND(B2=0,B1<>0),"subtotal = "&B1,"")
Copy C1 down through all rows of data.
Add a non-zero dummy number (99999) after the last data cell in column A (no
formulas in B or C next to the dummy number; it just ensures that the last
data cell in A gets handled correctly by the formulas in B & C).
Hide column B if desired.

Hope this helps,

Hutch