Instances of Occurrence of a unique item consecutively in excel

[Deniel]

I have an excel item list where the rows are listed with several text items in a particular column, where many of the items are appeared consecutively with gap in between. I am desperately trying to find a formula (without VBA) which will give a incremental number starting with 1 for each consecutive appearance of a particular no. at column B Pl. help. The example excel sheet is placed for the desired result.

 

[shinigamilight]

You will need 1 helper column for this one

Column B2: =IF(A2="A",IF(AND(A2="A",A1<>"A"),1,B1+1),"")
Column C2: =IF(A2="A",IF(A1="A",C1,SUMPRODUCT(--($B$2:B2=1))),"")

 

[Deniel]

You will need 1 helper column for this one

Column B2: =IF(A2="A",IF(AND(A2="A",A1<>"A"),1,B1+1),"")
Column C2: =IF(A2="A",IF(A1="A",C1,SUMPRODUCT(--($B$2:B2=1))),"")

This is what exactly I want. Your formula is amazing. I am trying to understand. Thank you so much.

 

[Fluff]

Another option without a helper column

Fluff.xlsm
	A	B
1
2
3
4	A	1
5	A	1
6	B
7	B
8	A	2
9	C
10	D
11	A	3
12	A	3
Data

Cell Formulas
Range	Formula
B4:B12	B4	=IF(AND(A4="A",A3="A"),B3,IF(A4="A",MAX(B$3:B3)+1,""))

 

[Sanjeev1976]

You can also try the below formula in cell B4 and then pulled down:

=IF(A4=A3,B3,MAXIFS($B3:B$3,$A3:A$3,A4)+1)

 

[Fluff]

The OP is using 2010 & therefore doesn't have the Maxifs function.

 

[Sanjeev1976]

Noted Fluff

 

[Deniel]

Another option without a helper column

Fluff.xlsm
	A	B
1
2
3
4	A	1
5	A	1
6	B
7	B
8	A	2
9	C
10	D
11	A	3
12	A	[FORMULA='=IF(AND(A12Goo
Data

Good alternative.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

Another variation

22 12 05.xlsm
	A	B
1
2
3
4	A	1
5	A	1
6	B
7	B
8	A	2
9	C
10	D
11	A	3
12	A	3
Count Groups

Cell Formulas
Range	Formula
B4:B12	B4	=IF(A4="A",IF(N(B3),B3,MAX(B$3:B3)+1),"")