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

 

[slam]

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

ah, my mistake. Entered as an array formula, but still just giving me the total instances rather than the highest consecutive streak.

 

[PaddyD]

forgot you were going across not down:

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

 

[slam]

Perfect, thank you!

 

[PaddyD]

You're welcome.

 

[slam]

One more question if I may.....

How would I combined that with the formula below to count consecutive instances that don't equal DNS or -

=SUMPRODUCT(((F2:X2<>"DNS")*(F2:X2<>"-")))

 

[PaddyD]

Give us a few examples of the sort of sequences you'll have and the results you expect.

 

[slam]

Example below.

The yellow text indicates the longest consecutive streak that doesn't include DNS or -, and the total is indicated in red text. The red text is what I need to automate.

 

[PaddyD]

Hhhmmm...

That's feels like it's going to bit a bit harder (i.e. not just a simple or() check for either string).

Not going to have much time over next week or so, so I'll see if I can solicit some help...

 

[slam]

I'm also trying to make this work for consecutive values >/= 15. Some help on that would also be greatly appreciated!

Thanks

 

[Aladin Akyurek]

Example below.

The yellow text indicates the longest consecutive streak that doesn't include DNS or -, and the total is indicated in red text. The red text is what I need to automate.

Hhhmmm...

That's feels like it's going to bit a bit harder (i.e. not just a simple or() check for either string).

Not going to have much time over next week or so, so I'll see if I can solicit some help...

Y2, control+shift+enter and copy down:

=MAX(FREQUENCY(IF(1-((F2:X2="DNS")+(F2:X2="-")),COLUMN(F2:X2)),
    IF((F2:X2="DNS")+(F2:X2="-"),COLUMN(F2:X2))))

Assumed that there will be no empty/blank cells.