Count consecutive value in a range using formula

[LalitPandey87]

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

[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?

[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

[LalitPandey87]

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

[LalitPandey87]

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:

[Al@ICP]

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!

[Admin]

Hi Al@ICP,

Welcome to ExcelFox !!

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