Count consecutive value in a range using formula

Hi All,

I have some values in a range in which according to criteria i need to count the continuous occurence of this value.

For ex.:-

A

B

A

B

A

A

A

B

and the answer is 3 (A A A) continuous occuring.


Thanks in Advance

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=279659#p279659
https://eileenslounge.com/viewtopic.php?p=279062#p279062
https://eileenslounge.com/viewtopic.php?p=278940#p278940
https://eileenslounge.com/viewtopic.php?p=276866#p276866
https://eileenslounge.com/viewtopic.php?p=276795#p276795
https://eileenslounge.com/viewtopic.php?p=276983#p276983
https://eileenslounge.com/viewtopic.php?p=277027#p277027
https://eileenslounge.com/viewtopic.php?p=277526#p277526
https://eileenslounge.com/viewtopic.php?p=277523#p277523
https://eileenslounge.com/viewtopic.php?p=277877#p277877
https://eileenslounge.com/viewtopic.php?p=278470#p278470
https://eileenslounge.com/viewtopic.php?p=275430#p275430
https://eileenslounge.com/viewtopic.php?p=275972#p275972
https://eileenslounge.com/viewtopic.php?p=276066#p276066
https://eileenslounge.com/viewtopic.php?p=276065#p276065
https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185
https://eileenslounge.com/viewtopic.php?p=276185#p276185
https://eileenslounge.com/viewtopic.php?p=276185#p276185
https://eileenslounge.com/viewtopic.php?p=276673#p276673
https://eileenslounge.com/viewtopic.php?p=276751#p276751
https://eileenslounge.com/viewtopic.php?p=276754#p276754
https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367
https://eileenslounge.com/viewtopic.php?p=274368#p274368
https://eileenslounge.com/viewtopic.php?p=274370#p274370
https://eileenslounge.com/viewtopic.php?p=274578#p274578
https://eileenslounge.com/viewtopic.php?p=274577#p274577
https://eileenslounge.com/viewtopic.php?p=274474#p274474
https://eileenslounge.com/viewtopic.php?p=274579#p274579
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

You mean the maximum continuous occurrence of a given value? Or should the character that is repeating the most continuously should be displayed along with the maximum number?

Hi

Try this array formula

=MAX(FREQUENCY(IF(A1:H1=J1,COLUMN(A1:H1)),IF(A1:H1 <>J1,COLUMN(A1:H1))))

Where J1 houses A

HTH

Quote:

---

Originally Posted by Excel Fox

You mean the maximum continuous occurrence of a given value? Or should the character that is repeating the most continuously should be displayed along with the maximum number?

---

Yes i mean the maximum continuous occurrence of a value based on criteria.

For Ex. :-

A

A

B

A

A

A

A

B

B

C

In above data range
if the criteria is "A" then the answer will be 4
if the criteria is "B" then the answer will be 2
if the criteria is "C" then the answer will be 1

Quote:

---

Originally Posted by Admin

Hi

Try this array formula

=MAX(FREQUENCY(IF(A1:H1=J1,COLUMN(A1:H1)),IF(A1:H1 <>J1,COLUMN(A1:H1))))

Where J1 houses A

HTH

---

Thanks Admin for your quick response.


This is exactly what i was lookin for. :cheers:

Quote:

---

Originally Posted by Admin

Hi

Try this array formula

=MAX(FREQUENCY(IF(A1:H1=J1,COLUMN(A1:H1)),IF(A1:H1 <>J1,COLUMN(A1:H1))))

Where J1 houses A

HTH

---

Hello,
I tired the same formula but it I got a #VALUE!

Hi Al@ICP,

Welcome to ExcelFox !!

It's an array formula. So confiirmed with CTRL+SHIFT+ENTER