I need a formula to subtotal each set of 1's in a column, without subtotaling
other numbers in the column.
example shown below.

1 )
1 ) subtotal = 2
2 ]
5 ]
6 ]
7 ] no subtotal
1 )
1 )
1 )
1 )
1 )
1 ) subtotal = 6
5 ]
6 ]
7 ]
6 ]
5 ] no subtotal
1 ) subtotal = 1

A B
1 =IF(AND(A1=1,A2<1),1,"")
1 =IF(AND(A2=1,A3<1),COUNTIF($A$1:$A2,1)-SUM($B$1:$B1),"")
2 =IF(AND(A3=1,A4<1),COUNTIF($A$1:$A3,1)-SUM($B$1:$B2),"")
5 =IF(AND(A4=1,A5<1),COUNTIF($A$1:$A4,1)-SUM($B$1:$B3),"")
6 =IF(AND(A5=1,A6<1),COUNTIF($A$1:$A5,1)-SUM($B$1:$B4),"")
7 =IF(AND(A6=1,A7<1),COUNTIF($A$1:$A6,1)-SUM($B$1:$B5),"")
1 =IF(AND(A7=1,A8<1),COUNTIF($A$1:$A7,1)-SUM($B$1:$B6),"")
1 =IF(AND(A8=1,A9<1),COUNTIF($A$1:$A8,1)-SUM($B$1:$B7),"")
1 =IF(AND(A9=1,A10<1),COUNTIF($A$1:$A9,1)-SUM($B$1:$B8),"")
1 =IF(AND(A10=1,A11<1),COUNTIF($A$1:$A10,1)-SUM($B$1:$B9),"")
1 =IF(AND(A11=1,A12<1),COUNTIF($A$1:$A11,1)-SUM($B$1:$B10),"")
1 =IF(AND(A12=1,A13<1),COUNTIF($A$1:$A12,1)-SUM($B$1:$B11),"")
5 =IF(AND(A13=1,A14<1),COUNTIF($A$1:$A13,1)-SUM($B$1:$B12),"")
6 =IF(AND(A14=1,A15<1),COUNTIF($A$1:$A14,1)-SUM($B$1:$B13),"")
7 =IF(AND(A15=1,A16<1),COUNTIF($A$1:$A15,1)-SUM($B$1:$B14),"")
6 =IF(AND(A16=1,A17<1),COUNTIF($A$1:$A16,1)-SUM($B$1:$B15),"")
5 =IF(AND(A17=1,A18<1),COUNTIF($A$1:$A17,1)-SUM($B$1:$B16),"")
1 =IF(AND(A18=1,A19<1),COUNTIF($A$1:$A18,1)-SUM($B$1:$B17),"")

B1 is a unique formula being the first cell. The formula in B2 can be
copied down.
-- .
If this helps, please remember to click yes.


"Katie" wrote:

I need a formula to subtotal each set of 1's in a column, without subtotaling
other numbers in the column.
example shown below.

1 )
1 ) subtotal = 2
2 ]
5 ]
6 ]
7 ] no subtotal
1 )
1 )
1 )
1 )
1 )
1 ) subtotal = 6
5 ]
6 ]
7 ]
6 ]
5 ] no subtotal
1 ) subtotal = 1

i ended up with a column that looked like this
0












1










0



0


0

not quite wat i had in mind..

--------------
"Paul C" wrote:


A B
1 =IF(AND(A1=1,A2<1),1,"")
1 =IF(AND(A2=1,A3<1),COUNTIF($A$1:$A2,1)-SUM($B$1:$B1),"")
2 =IF(AND(A3=1,A4<1),COUNTIF($A$1:$A3,1)-SUM($B$1:$B2),"")
5 =IF(AND(A4=1,A5<1),COUNTIF($A$1:$A4,1)-SUM($B$1:$B3),"")
6 =IF(AND(A5=1,A6<1),COUNTIF($A$1:$A5,1)-SUM($B$1:$B4),"")
7 =IF(AND(A6=1,A7<1),COUNTIF($A$1:$A6,1)-SUM($B$1:$B5),"")
1 =IF(AND(A7=1,A8<1),COUNTIF($A$1:$A7,1)-SUM($B$1:$B6),"")
1 =IF(AND(A8=1,A9<1),COUNTIF($A$1:$A8,1)-SUM($B$1:$B7),"")
1 =IF(AND(A9=1,A10<1),COUNTIF($A$1:$A9,1)-SUM($B$1:$B8),"")
1 =IF(AND(A10=1,A11<1),COUNTIF($A$1:$A10,1)-SUM($B$1:$B9),"")
1 =IF(AND(A11=1,A12<1),COUNTIF($A$1:$A11,1)-SUM($B$1:$B10),"")
1 =IF(AND(A12=1,A13<1),COUNTIF($A$1:$A12,1)-SUM($B$1:$B11),"")
5 =IF(AND(A13=1,A14<1),COUNTIF($A$1:$A13,1)-SUM($B$1:$B12),"")
6 =IF(AND(A14=1,A15<1),COUNTIF($A$1:$A14,1)-SUM($B$1:$B13),"")
7 =IF(AND(A15=1,A16<1),COUNTIF($A$1:$A15,1)-SUM($B$1:$B14),"")
6 =IF(AND(A16=1,A17<1),COUNTIF($A$1:$A16,1)-SUM($B$1:$B15),"")
5 =IF(AND(A17=1,A18<1),COUNTIF($A$1:$A17,1)-SUM($B$1:$B16),"")
1 =IF(AND(A18=1,A19<1),COUNTIF($A$1:$A18,1)-SUM($B$1:$B17),"")

B1 is a unique formula being the first cell. The formula in B2 can be
copied down.
-- .
If this helps, please remember to click yes.


"Katie" wrote:

I need a formula to subtotal each set of 1's in a column, without subtotaling
other numbers in the column.
example shown below.

1 )
1 ) subtotal = 2
2 ]
5 ]
6 ]
7 ] no subtotal
1 )
1 )
1 )
1 )
1 )
1 ) subtotal = 6
5 ]
6 ]
7 ]
6 ]
5 ] no subtotal
1 ) subtotal = 1

my results look like this
A B
1 Row 1
1 2 Row 2
2 Row 3
5 Row 4
6 Row 5
7 Row 6
1 Row 7
1 Row 8
1 Row 9
1 Row 10
1 Row 11
1 6 Row 12
5 Row 13
6 Row 14
7 Row 15
6 Row 16
5 Row 17
1 1 Row 18

Double check the formula in B2.
=IF(AND(A2=1,A3<1),COUNTIF($A$1:$A2,1)-SUM($B$1:$B1),"")

Tthe absolute reference indicators ($) are critical to make the formula work
when you copy down. One missing or extra $ will change the results
--
If this helps, please remember to click yes.


"Katie" wrote:

i ended up with a column that looked like this
0












1










0



0


0

not quite wat i had in mind..

--------------
"Paul C" wrote:


A B
1 =IF(AND(A1=1,A2<1),1,"")
1 =IF(AND(A2=1,A3<1),COUNTIF($A$1:$A2,1)-SUM($B$1:$B1),"")
2 =IF(AND(A3=1,A4<1),COUNTIF($A$1:$A3,1)-SUM($B$1:$B2),"")
5 =IF(AND(A4=1,A5<1),COUNTIF($A$1:$A4,1)-SUM($B$1:$B3),"")
6 =IF(AND(A5=1,A6<1),COUNTIF($A$1:$A5,1)-SUM($B$1:$B4),"")
7 =IF(AND(A6=1,A7<1),COUNTIF($A$1:$A6,1)-SUM($B$1:$B5),"")
1 =IF(AND(A7=1,A8<1),COUNTIF($A$1:$A7,1)-SUM($B$1:$B6),"")
1 =IF(AND(A8=1,A9<1),COUNTIF($A$1:$A8,1)-SUM($B$1:$B7),"")
1 =IF(AND(A9=1,A10<1),COUNTIF($A$1:$A9,1)-SUM($B$1:$B8),"")
1 =IF(AND(A10=1,A11<1),COUNTIF($A$1:$A10,1)-SUM($B$1:$B9),"")
1 =IF(AND(A11=1,A12<1),COUNTIF($A$1:$A11,1)-SUM($B$1:$B10),"")
1 =IF(AND(A12=1,A13<1),COUNTIF($A$1:$A12,1)-SUM($B$1:$B11),"")
5 =IF(AND(A13=1,A14<1),COUNTIF($A$1:$A13,1)-SUM($B$1:$B12),"")
6 =IF(AND(A14=1,A15<1),COUNTIF($A$1:$A14,1)-SUM($B$1:$B13),"")
7 =IF(AND(A15=1,A16<1),COUNTIF($A$1:$A15,1)-SUM($B$1:$B14),"")
6 =IF(AND(A16=1,A17<1),COUNTIF($A$1:$A16,1)-SUM($B$1:$B15),"")
5 =IF(AND(A17=1,A18<1),COUNTIF($A$1:$A17,1)-SUM($B$1:$B16),"")
1 =IF(AND(A18=1,A19<1),COUNTIF($A$1:$A18,1)-SUM($B$1:$B17),"")

B1 is a unique formula being the first cell. The formula in B2 can be
copied down.
-- .
If this helps, please remember to click yes.


"Katie" wrote:

I need a formula to subtotal each set of 1's in a column, without subtotaling
other numbers in the column.
example shown below.

1 )
1 ) subtotal = 2
2 ]
5 ]
6 ]
7 ] no subtotal
1 )
1 )
1 )
1 )
1 )
1 ) subtotal = 6
5 ]
6 ]
7 ]
6 ]
5 ] no subtotal
1 ) subtotal = 1