Subtotaling

[Katie]

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

 

[Paul C]

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]

i ended up with a column that looked like this
0












1










0



0


0

not quite wat i had in mind..

--------------

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.

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

 

[Paul C]

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.

i ended up with a column that looked like this
0












1










0



0


0

not quite wat i had in mind..

--------------

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.

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