Count the number of cells between values

[imagana]

I am trying to count the number of cells between values

I have a column with a lot of 0's & 1's, I want the count of 0's between every 1 using a formula if possible.

Thank you for your help

Idalia

 

[JoeMo]

Copy formula in B2 down.

Excel Workbook
	A	B
1	1
2	0
3	0
4	0
5	0
6	1	4
7	0
8	1	1
9	0
10	0
11	0
12	0
13	0
14	0
15	1	6
16	0
17	1	1
18	1	0
19	0
20	1	1
Sheet1

 

[iliace]

I'm not sure how you want to present this calculation - what do envision this output will look like?

Here is something I put together. It will give you the number of zeros for each occurrence of 1, after the first one. Note, formula for D5 and D6 are different; D6 is copied down. Formula in E5 is just copied straight down.

The result in column D displays how many zeros there were before this current occurrence of 1. Column E is the row number of the current occurrence of 1, within the given data range (so not counting the header row).

Attempting to count more 1s than are in the data range will give an #N/A error.

Sheet3

	A	B	C	D	E
	One #1
	One #2
	One #3
	One #4
	One #5
	One #6
	One #7
	One #8

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Value[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]One Row[/TD]
[TD="align: center"]In Row[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]25[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]28[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="align: right"]1[/TD]

</tbody>

Worksheet formulas

Cell Formula D5 =MATCH(1, $A$2:$A$29, 0)-1 E5 =SUM($D$5:D5)+COUNTA($D$5:D5) D6 =MATCH(1, OFFSET($A$2:$A$29, E5, 0), 0)-1 <tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

Copy formula in B2 down.
Sheet1

	A	B

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]19[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =IF(A2<>1,"",COUNT(A$1:A2)-SUM(A$1:A2)-SUM(B$1:B1)) <tbody> </tbody>

<tbody>
</tbody>

Using your same setup, here is another formula that can be placed in cell B2 and copied down...

=IF(A2=1,COUNTIF(A$1:A1,"0")-SUM(B$1:B1),"")

As an aside, how do you get Excel Jeannie to leave the grid lines in when you paste from it into a response here... whenever I do it, the grid lines disappear.

 

[imagana]

Thank you so much these all helped. How can I modify the formula to get the count of 1's between 0's?

 

[Rick Rothstein]

Thank you so much these all helped. How can I modify the formula to get the count of 1's between 0's?

Does this modification to my formula do what you are now asking for...

=IF(A2=0,COUNTIF(A$1:A1,"1")-SUM(B$1:B1),"")