Count Consecutive Cells Containing specific text?

slam

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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

slam

Well-known Member
Joined
Sep 16, 2002
Messages
810
Office Version
  1. 365
  2. 2019
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,647
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
810
Office Version
  1. 365
  2. 2019
- 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,647

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
810
Office Version
  1. 365
  2. 2019
=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))))
 

Forum statistics

Threads
1,147,734
Messages
5,742,863
Members
423,760
Latest member
photogfrog

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
Top