|
Subtotaling
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 |
Subtotaling
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 |
Subtotaling
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 |
Subtotaling
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 |
| All times are GMT +1. The time now is 07:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com