Count Consecutive Cells Containing specific text?

slam

Well-known Member
Joined
Sep 16, 2002
Messages
848
Office Version
  1. 365
  2. 2019
Let's say in F2 to X2, I sometimes have the text DNS. In AN2, How can I count the consecutive instances of DNS?

Thanks
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
jproffer - I already have that part. That gives the number of instances. Now I'm looking for the highest number of consecutive instances.

Thanks
 
Upvote 0
I see, I see...misunderstood. Sorry, it will probably be a UDF...I'll see what I can do.
 
Upvote 0
- could there be more than 1 instance of the repeated series?
- if so, what should happen?
 
Upvote 0
- could there be more than 1 instance of the repeated series?
- if so, what should happen?

There could be, but I'm only interested in the highest consecutive number. If DNS appeared twice in a row, followed by some other text, then it appeared 4 times in a row, I would want 4 displayed.
 
Upvote 0
Try this and see if it does what you want. If not, it can be adjusted.

Code:
Function CountConsecutive(MyString As String, MyRange As Range)
  Dim Count As Long, MaxCount As Long, Cll As Range
 
  For Each Cll In MyRange
    If Cll.Value = MyString Then
      Count = Count + 1
    Else
      If Count > MaxCount Then MaxCount = Count
      Count = 0
    End If
  Next Cll
 
  CountConsecutive = MaxCount
 
End Function

BTW, this was found on...........I think Ozgrid. This is NOT my original work/concept. :)
 
Upvote 0
max(frequency(if(range=condition,row(range)),if(range< > condition,row(range))))

...entered with control + shift + enter, not just enter.
 
Upvote 0
=max(frequency(if(F2:X2="DNS",2(F2:X2)),if(F2:X2<>"DNS",2(F2:X2))))

?
 
Upvote 0
the row() component is a function (confusing, I guess, given my use of 'range' as well):

=max(frequency(if(F2:X2="DNS",row(F2:X2)),if(F2:X2<>"DNS",row(F2:X2))))
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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