Count Consecutive Cells Containing specific text?

slam

Well-known Member
Joined
Sep 16, 2002
Messages
772
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:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

slam

Well-known Member
Joined
Sep 16, 2002
Messages
772
jproffer - I already have that part. That gives the number of instances. Now I'm looking for the highest number of consecutive instances.

Thanks
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
I see, I see...misunderstood. Sorry, it will probably be a UDF...I'll see what I can do.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

- could there be more than 1 instance of the repeated series?
- if so, what should happen?
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
772
- 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.
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643

ADVERTISEMENT

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. :)
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
max(frequency(if(range=condition,row(range)),if(range< > condition,row(range))))

...entered with control + shift + enter, not just enter.
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
772
=max(frequency(if(F2:X2="DNS",2(F2:X2)),if(F2:X2<>"DNS",2(F2:X2))))

?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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))))
 

Watch MrExcel Video

Forum statistics

Threads
1,113,890
Messages
5,544,887
Members
410,643
Latest member
sng
Top