Count Consecutive Cells Containing specific text?

[slam]

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

 

[jproffer]

=countif(f2:x2,"=dns")

 

[slam]

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]

I see, I see...misunderstood. Sorry, it will probably be a UDF...I'll see what I can do.

 

[PaddyD]

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

 

[slam]

- 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]

Try this and see if it does what you want. If not, it can be adjusted.

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]

max(frequency(if(range=condition,row(range)),if(range< > condition,row(range))))

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

 

[slam]

=max(frequency(if(F2:X2="DNS",2(F2:X2)),if(F2:X2<>"DNS",2(F2:X2))))

?

 

[PaddyD]

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