Count of Values (Rows) of Most Recent Consecutive Streak (2+ Distinct Values)

1. Count of Values (Rows) of Most Recent Consecutive Streak (2+ Distinct Values)

Hi Everyone,
I have a column of data that looks like this (assume A1:A9):

11
88
88
11
44
44
88
88
88

I'm looking for a formula in column B to calculate the count of values (rows) of most recent consecutive streak of a value; i.e. B9 would calculate '3' for 88, '2' for 44 and '1' for 11. My data is chronological in ascending order.

I've been scratching my head and google searching for the correct combination of Count, Rows, Frequency, etc. functions to get the correct result - but struggling...

Thanks for any and all effort!

2. Re: Count of Values (Rows) of Most Recent Consecutive Streak (2+ Distinct Values)

With data starting in row 2

in B2

=IF(A2=A1,B1+1,1)

will give the following results ....

x y
11 1
88 1
88 2
11 1
44 1
44 2
88 1
88 2
88 3
44 1
44 2
44 3
44 4

With the following in D2 down i.e. 11, 44, 88

then in E2

=MAX(IF(\$A\$2:\$A\$14=\$D2,\$B\$2:\$B\$14))

Enter above with Ctrl+Shift+Enter

D E
11 1
44 4
88 3

Hope this helps.

3. Re: Count of Values (Rows) of Most Recent Consecutive Streak (2+ Distinct Values)

Hi John,
Thank you for taking the time to reply to my post!

Your recommendation has worked for my purposes. I appreciate it!

There are currently 1 users browsing this thread. (0 members and 1 guests)