count 1 for 15 or more consecutive 1s

D4ND4N

New Member
Joined
Oct 1, 2014
Messages
5
Guys,

I have a column consisting of 1s and 0s/spaces. I want to count 1 for every 15 or more consecutive 1s. The result should be the number of times of consecutive fifteen or more 1s appearing on the list. What would the function be?

Please help me on this. TY.

Regards,
D4ND4N
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Try this ...

Code:
'this subroutine counts the no. of consecutive 1's appearing in a column range
Sub countONES()
    
    Dim lastrow As Long         ' denotes the last NON-EMPTY cell in the column range
    Dim count  As Long          ' increments each time upon encountering a 1
    Dim counter As Long         ' increments each time upon encountering 15 consecutive 1's or more
    Dim startcell As String     ' used when extracting the column name in the messagebox
    Dim pos1 As Integer         ' position of the "$" sign in the cell address -> startcell
    Dim pos2 As Integer         ' position of the 2nd "$" sign in the cell address -> startcell
    
    'intialise counter
    counter = 0
    counter = 0
    
    'find the last NON-EMPTY cell in the column
    lastrow = Range("A65536").End(xlUp).Row
    
    'select the first cell in the column (in this case Column A - you can change this)
    Range("A1").Select
    startcell = ActiveCell.Address
    pos1 = InStr(1, startcell, "$")
    pos2 = InStrRev(startcell, "$", -1)
    
    For icount = 1 To lastrow
        If ActiveCell.Value = 1 Then
            'increment count each time when you encounter a 1
            count = count + 1
            
            If count >= 15 Then
                'increment counter each time when you encounter 15 consecutive 1's
                counter = counter + 1
                
                'reset count - why ? - need to start over again so as to keep track of the next 15 consecutive 1's
                count = 0
            End If
            
        Else
            'reset count - why ? - need to start over again so as to keep track of the next 15 consecutive 1's
             count = 0
        End If
             
        'select the next cell
        ActiveCell.Offset(1, 0).Select
 
    Next icount
    
    MsgBox "The no. of 15 consecutive 1's in column " & Mid(startcell, pos1 + 1, pos2 - pos1 - 1) & " = " & counter, vbInformation, "Consecutive 1's ?"
    
    
End Sub

Guys,

I have a column consisting of 1s and 0s/spaces. I want to count 1 for every 15 or more consecutive 1s. The result should be the number of times of consecutive fifteen or more 1s appearing on the list. What would the function be?

Please help me on this. TY.

Regards,
D4ND4N
 
Last edited:
Upvote 0
=COUNT(1/(FREQUENCY(($A$1:$A$100=1)*ROW($A$1:$A$100),($A$1:$A$100<>1)*ROW($A$1:$A$100))>14))
which must be array entered using Ctrl+Shift+Enter
 
Upvote 0
Hi.

Can "consecutive" include blanks in between, or do the the rows with 1s have to be strictly following each other?

For example, does:

1/1/1/1/1/[blank]/1/1/1/1/1/1/1/1/[blank]/[blank]/1/1

count as a "consecutive" string of 1s?

Regards
 
Upvote 0
=COUNT(1/(FREQUENCY(($A$1:$A$100=1)*ROW($A$1:$A$100),($A$1:$A$100<>1)*ROW($A$1:$A$100))>14))
which must be array entered using Ctrl+Shift+Enter

This is not the most rigorous construction.

There's a strong probability that this will give an answer which is one higher than the correct value, since, if you don't use an IF clause, the zeroes in each of your arrays being passed to FREQUENCY will equally be considered, and so the result for that formula for a bin of 0 may well be over 14.

Boolean FALSEs, however, are not considered by FREQUENCY.

Regards
 
Last edited:
Upvote 0
Good point. I had actually originally written it with two IF clauses and then changed it just for the hell of it. Probably should have tested it again afterwards!
 
Upvote 0
Good point. I had actually originally written it with two IF clauses and then changed it just for the hell of it. Probably should have tested it again afterwards!

Done it myself before! That's how I knew! :)
 
Upvote 0
If there are More than 15 consecutive 1's, say 17
Should that be counted as 1 occurance, or 3 (1-15, 2-16, 3-17) ?

This will count them as multiple
=SUMPRODUCT(--(COUNTIF(OFFSET(A1,ROW(A1:A100)-1,0,15,1),1)=15))
 
Upvote 0
:)

Just for D4ND4N, this is how I originally had it (and should have left it!):

=COUNT(1/(FREQUENCY(IF($A$1:$A$100=1,ROW($A$1:$A$100)),IF($A$1:$A$100<>1,ROW($A$1:$A$100)))>14))
again, array entered with Ctrl+Shift+Enter.
 
Upvote 0
For what it's worth, here's an updated version of my last post that will count more than 15 consecutive as 1 occurance.

=SUMPRODUCT(--(COUNTIF(OFFSET(A1,ROW(A1:A100)-1,0,15,1),1)=15),--(COUNTIF(OFFSET(A2,ROW(A1:A100)-1,0,15,1),1)<>15))

Non Array entered, though the use of OFFSET probably cancels out the benefit of being Non Array entered...
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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