Counting streaks in Excel

Hello,

I'm currently using this formula =MAX(FREQUENCY(IF(C:C>8,ROW(C:C)),IF(C:C<=8,ROW(C:C)))) to count streaks where column C is higher than 8.

In column A I have a list of team names that have received the scores - its in a random order.

Is it possible to count streaks where scores are above 8 for certain names in column A?

May you provide a sample workbook - I am not sure what it is that you require? Your formula does not count streaks but return the longest streak.

Thats perfect! Thanks everyone for the help and sorry the explanation wasn't great.

Originally Posted by Tony Valko

The names would have to be sorted or grouped together.

For example:


Data Range

	A	B	C	D	E
1	Name	Score	-----	Name	Consecutives
2	Bill	1		Bill	3
3	Bill	8
4	Bill	8
5	Bill	8
6	Bill	5
7	Karen	2
8	Karen	8
9	Karen	8
10	Karen	2

This array formula** entered in E2:

=MAX(FREQUENCY(IF(A2:A10=D2,IF(B2:B10=8,ROW(B2:B10))),IF(A2:A10<>D2,IF(B2:B10<>8,ROW(B2:B10)))))



** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Hi, wondering if anyone can help having some issues with this formula

Using the previous example with Bill and Karen's scores.

But say Bill got another score of 8 and this was added to the data(A11,B11), in E2 it would no longer show 3, it would show 4... Counting how many 8s Bill got, not what the streak of 8s was.

Im curious because in the spreadsheet I wish to use this in, all of the name info is the same, so the formula is just counting how many times the required outcome occurs not what the longest streak is.

Do I need a different formula or is there a simple change that can be made to this one?

Thanks

ahh no worries didn't realise, sorted it now anyway