Counting Consecutive Year

[myexcel211]

Hi,

Could anyone help. I would Like to count the consecutive years of a client in excel. Example below,
Client 1 would be 6 as that is how many years in sequential order it appear in a row
Client 2 would be 2 as that is how many years in sequential order it appear in a row.
Anyone has any formula that would help me? Thanks!

Year	Client
2009​	1​
2010​	1​
2011​	1​
2012​	1​
2013​	1​
2014​	1​
2009​	2​
2010​	2​
2011​	1​
2012​	2​
2013​	1​
2014​	2​

 

[hrayani]

Hi, try this

Book1
	A	B	C	D
1	Year	Client	Client	Answer
2	2009	1	1	6
3	2010	1	2	2
4	2011	1
5	2012	1
6	2013	1
7	2014	1
8	2009	2
9	2010	2
10	2011	1
11	2012	2
12	2013	1
13	2014	2
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=MAX(FREQUENCY(IF($B$2:$B$13=C2,ROW($B$2:$B$13)),IF($B$2:$B$13<>C2,ROW($B$2:$B$13))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[myexcel211]

Hi, try this

Book1
	A	B	C	D
1	Year	Client	Client	Answer
2	2009	1	1	6
3	2010	1	2	2
4	2011	1
5	2012	1
6	2013	1
7	2014	1
8	2009	2
9	2010	2
10	2011	1
11	2012	2
12	2013	1
13	2014	2
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=MAX(FREQUENCY(IF($B$2:$B$13=C2,ROW($B$2:$B$13)),IF($B$2:$B$13<>C2,ROW($B$2:$B$13))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you so much for your insight. Unfortunately, it didn't produce the outcomes that I was looking for. This formula did give outcomes based on the data column B. What was looking for is, the years that appear in orders: 2009,2010,2011,2012...etc. Thanks again. I will use this to work around.