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

P

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>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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.
 
Upvote 0
How about:

Excel 2012
BCD

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="align: center"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=SUMPRODUCT(--(B2:B50=1),--(B3:B51=0),--(B4:B52=1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks. Sorry, my apologies. Maybe I didn't make myself clear enough. Pls see below. Thanks.

lLjjgQ7.jpg
[/URL][/IMG]
 
Upvote 0
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
 
Upvote 0
Something like this then?

Excel 2012
BCD

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

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

[TD="align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

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

[TD="align: center"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2:D16[/TH]
[TD="align: left"]{=FREQUENCY(IF(B2:B50=0,ROW(B2:B50)),IF(B2:B50=1,ROW(B2:B50)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Numbers​
[/td][td]
Result​
[/td][td][/td][td]
Just to check​
[/td][td]
Sum​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
0​
[/td][td]
24​
[/td][td][/td][td][/td][td]
24​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
0​
[/td][td][/td][td][/td][td]
2​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
0​
[/td][td][/td][td][/td][td]
6​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td]
0​
[/td][td][/td][td][/td][td]
1​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
19​
[/td][td]
0​
[/td][td][/td][td][/td][td]
3​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
20​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
21​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
22​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
23​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
24​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
25​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
26​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
27​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
28​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
29​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
30​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
31​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
32​
[/td][td]
0​
[/td][td][/td][td][/td][td]
12​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
33​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
34​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
35​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
36​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
37​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
38​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.
 
Upvote 0
If you want a formula to drag down, this also could work:

Excel 2012
BCD

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

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

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

[TD="align: center"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(B3=1,ROW()-LOOKUP(2,1/(B$2:B2=1),ROW(B$2:B2)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top