Formula to count the number of 0s in between two 1s as you go down the rows for a column

pc732015

New Member
Joined
Oct 6, 2017
Messages
14
<ytd-expander id="expander" class="style-scope ytd-comment-renderer" collapsed="" style="display: block; --ytd-expander-collapsed-height:80px;"><yt-formatted-string id="content-text" slot="content" split-lines="" class="style-scope ytd-comment-renderer" style="white-space: pre-wrap; --yt-endpoint-color:var(--yt-spec-call-to-action); --yt-endpoint-hover-color:var(--yt-spec-call-to-action); --yt-endpoint-visited-color:var(--yt-spec-call-to-action); color: var(--ytd-comment-text-color); font-size: 1.4rem; line-height: 2rem;">Hi, I have a column that has 1s and 0s and the column contains a few hundreds of these 1s and 0s, as can be seen in a sample of the data in https://i.imgur.com/Y4GN8BN.jpg. What should be the formula to use that will count the number of 0s in between two 1s as you go down the rows for this column? Thanks.</yt-formatted-string>
</ytd-expander><ytd-comment-action-buttons-renderer id="action-buttons" class="style-scope ytd-comment-renderer" action-buttons-style="desktop-toolbar" style="display: block; color: var(--yt-spec-text-secondary); min-height: 16px; margin-top: 4px;">
<ytd-toggle-button-renderer id="like-button" class="style-scope ytd-comment-action-buttons-renderer style-text size-default" button-renderer="" new-subscribe-color="" is-icon-button="" has-no-text="" style="display: inline-block; text-transform: uppercase; --paper-button-ink-color:var(--yt-spec-icon-inactive); --yt-button-padding:10px 16px; --yt-button-margin:0; --yt-button-padding-minus-border:9px 15px; vertical-align: middle; font-size: 1.3rem; letter-spacing: 0.007px; color: var(--yt-spec-icon-inactive); --yt-button-icon-size:var(--ytd-comment-thumb-dimension); margin-left: -8px;"><yt-icon-button id="button" class="style-scope ytd-toggle-button-renderer style-text size-default" aria-pressed="false" style="display: inline-block; position: relative; width: var(--yt-button-icon-size, var(--yt-icon-width, 40px)); height: var(--yt-button-icon-size, var(--yt-icon-height, 40px)); box-sizing: border-box; font-size: 0px; padding: var(--yt-button-icon-padding, 8px); color: var(--yt-button-color, inherit); line-height: 1; background-color: transparent; text-transform: inherit;"><button id="button" class="style-scope yt-icon-button" aria-label="like this comment along with 0 other people" style="vertical-align: middle; outline: none; background-image: none; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; margin: 0px; border-width: initial; border-style: none; border-color: initial; padding: 0px; width: 16px; height: 16px; line-height: 0; cursor: pointer; -webkit-tap-highlight-color: transparent;"><yt-icon class="style-scope ytd-toggle-button-renderer" style="display: inline-flex; align-items: center; justify-content: center; position: relative; vertical-align: middle; fill: var(--iron-icon-fill-color, currentcolor); stroke: none; margin-left: var(--iron-icon_-_margin-left); margin-bottom: var(--iron-icon_-_margin-bottom); margin-right: var(--iron-icon_-_margin-right); width: var(--yt-icon-button-icon-width, 100%); height: var(--yt-icon-button-icon-height, 100%);"><svg viewBox="0 0 24 24" preserveAspectRatio="xMidYMid meet" focusable="false" class="style-scope yt-icon" style="pointer-events: none; display: block; width: 100%; height: 100%;"><g class="style-scope yt-icon"></g></svg>
</yt-icon></button></yt-icon-button></ytd-toggle-button-renderer><ytd-toggle-button-renderer id="dislike-button" class="style-scope ytd-comment-action-buttons-renderer style-text size-default" button-renderer="" new-subscribe-color="" is-icon-button="" has-no-text="" style="display: inline-block; text-transform: uppercase; --paper-button-ink-color:var(--yt-spec-icon-inactive); --yt-button-padding:10px 16px; --yt-button-margin:0; --yt-button-padding-minus-border:9px 15px; vertical-align: middle; font-size: 1.3rem; letter-spacing: 0.007px; color: var(--yt-spec-icon-inactive); --yt-button-icon-size:var(--ytd-comment-thumb-dimension);"><yt-icon-button id="button" class="style-scope ytd-toggle-button-renderer style-text size-default" aria-pressed="false" style="display: inline-block; position: relative; width: var(--yt-button-icon-size, var(--yt-icon-width, 40px)); height: var(--yt-button-icon-size, var(--yt-icon-height, 40px)); box-sizing: border-box; font-size: 0px; padding: var(--yt-button-icon-padding, 8px); color: var(--yt-button-color, inherit); line-height: 1; background-color: transparent; text-transform: inherit;"><button id="button" class="style-scope yt-icon-button" aria-label="Dislike this comment" style="vertical-align: middle; outline: none; background-image: none; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; margin: 0px; border-width: initial; border-style: none; border-color: initial; padding: 0px; width: 16px; height: 16px; line-height: 0; cursor: pointer; -webkit-tap-highlight-color: transparent;"><yt-icon class="style-scope ytd-toggle-button-renderer" style="display: inline-flex; align-items: center; justify-content: center; position: relative; vertical-align: middle; fill: var(--iron-icon-fill-color, currentcolor); stroke: none; margin-left: var(--iron-icon_-_margin-left); margin-bottom: var(--iron-icon_-_margin-bottom); margin-right: var(--iron-icon_-_margin-right); width: var(--yt-icon-button-icon-width, 100%); height: var(--yt-icon-button-icon-height, 100%);"><svg viewBox="0 0 24 24" preserveAspectRatio="xMidYMid meet" focusable="false" class="style-scope yt-icon" style="pointer-events: none; display: block; width: 100%; height: 100%;"><g class="style-scope yt-icon"></g></svg>
</yt-icon></button></yt-icon-button></ytd-toggle-button-renderer>
<ytd-button-renderer class="style-scope ytd-comment-action-buttons-renderer style-text size-default" button-renderer="" new-subscribe-color="" is-paper-button="" style="--paper-button-ink-color:var(--yt-paper-button-ink-color); --yt-formatted-string-deemphasize-color:#FFFFFF; --yt-formatted-string-deemphasize_-_margin-left:4px; --yt-formatted-string-deemphasize_-_display: initial; vertical-align: middle; white-space: nowrap; font-size: 1.3rem; letter-spacing: 0.007px; text-transform: uppercase; display: inline-block; background-color: var(--yt-basic-background-color, ); color: var(--yt-spec-text-secondary); border-color: var(--yt-basic-foreground-title-color, var(--yt-spec-text-secondary)); border-radius: var(--yt-button-border-radius, 3px); margin: var(--yt-button-margin, 0 0.29em); --yt-button-padding:8px 16px;">
<yt-formatted-string id="text" class="style-scope ytd-button-renderer style-text size-default" style="display: block; overflow: hidden; text-overflow: ellipsis;">REPLY</yt-formatted-string>

</ytd-button-renderer>


</ytd-comment-action-buttons-renderer>

<ytd-menu-renderer class="style-scope ytd-comment-renderer" style="display: var(--layout-horizontal_-_display); flex-direction: var(--layout-horizontal_-_flex-direction);"></ytd-menu-renderer>
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

pc732015

New Member
Joined
Oct 6, 2017
Messages
14
Thanks. But when I used such a formula, it gives me 0 or 1 only. When you move down the rows, as you can see from https://i.imgur.com/Y4GN8BN.jpg, the number of 0s between two 1s are very random, I guess the formula must be more complex than the one that you have suggested. I appreciate your help though :). Can anybody else help? Thanks.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,754
How about:

Excel 2012
BCD
212
30
41
50
60
71
80
90
100
110
120
130
141
150
161
170
180
190
201
210
220
230
240
250
260
270
280
290
300
310
320
331
341
350
360
370
380

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
D2=SUMPRODUCT(--(B2:B50=1),--(B3:B51=0),--(B4:B52=1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

pc732015

New Member
Joined
Oct 6, 2017
Messages
14

ADVERTISEMENT

Thanks. Sorry, my apologies. Maybe I didn't make myself clear enough. Pls see below. Thanks.

lLjjgQ7.jpg
[/URL][/IMG]
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
So what's your expected result for the example you posted?

And why isn't the 0 in B3 considered for the count? Or the three zeroes in B17:B19? Etc., etc.

Regards
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,754

ADVERTISEMENT

Something like this then?

Excel 2012
BCD
210
301
412
506
601
713
8012
900
10016
110#N/A
120#N/A
130#N/A
141#N/A
150#N/A
161#N/A
170
180
190
201
210
220
230
240
250
260
270
280
290
300
310
320
331
341
350
360
370
380

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Array Formulas
CellFormula
D2:D16{=FREQUENCY(IF(B2:B50=0,ROW(B2:B50)),IF(B2:B50=1,ROW(B2:B50)))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



Select the range D2:D16 (for example), enter the formula and confirm with Control+Shift+Enter. The first value will be 0, but the rest of the values going down the column should be the values you're looking for. If you have hundreds of rows, you will need to enter the formula in a large range. The will be #N/A errors at the end when it runs out of data.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe something like this


B
C
D
E
F
1
Numbers​
Result​
Just to check​
Sum​
2
0​
24​
24​
3
0​
4
1​
5
0​
6
0​
2​
7
1​
8
0​
9
0​
10
0​
11
0​
12
0​
13
0​
6​
14
1​
15
0​
1​
16
1​
17
0​
18
0​
19
0​
3​
20
1​
21
0​
22
0​
23
0​
24
0​
25
0​
26
0​
27
0​
28
0​
29
0​
30
0​
31
0​
32
0​
12​
33
1​
34
1​
35
0​
36
0​
37
0​
38
0​

Assumes data in Sheet1
Create a named range
Formulas > Names manager > New
Name: RngData
Refers to: =INDEX(Sheet1!$B:$B,MATCH(1,Sheet1!$B:$B,0)):INDEX(Sheet1!$B$2:$B$1000,LOOKUP(2,1/(Sheet1!$B$2:$B$1000=1),ROW(Sheet1!$B$2:$B$1000)-ROW(Sheet1!$B$2)+1))

Array formula in B2
=SUM(FREQUENCY(IF(RngData=0,ROW(RngData)),IF(RngData=1,ROW(RngData))))

Formula in F2 (check)
=SUM(E:E)

M.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,754
If you want a formula to drag down, this also could work:

Excel 2012
BCD
21
301
41
50
602
71
80
90
100
110
120
1306
141
1501
161
170
180
1903
201
210
220
230
240
250
260
270
280
290
300
310
32012
3310
341
350
360
370
380

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
D2=IF(B3=1,ROW()-LOOKUP(2,1/(B$2:B2=1),ROW(B$2:B2)),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,881
Office Version
  1. 2010
Platform
  1. Windows
Here is another formula placed in cell C2 that can be dragged down to the bottom of your data which will produce an output like Eric shows for his formula in Message #9 ...

=IF(B3=1,COUNTIF(B$2:B2,0)-SUM(C$1:C1),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,647
Messages
5,626,073
Members
416,160
Latest member
SanbiVN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top