calculating subtotals for sequential blank cells in 1 column

K

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.
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top