Excel Formula Question

[pr50]

Hello,

I am trying to figure out a formula that would look at the data in A and B and produce C.

Basically, if A2 and B2 are different than A3 and B3, then C3 would increase by 1. Once A changes to a new value, the count on C would start over.

I hope this makes sense. Any help would be greatly appreciated!!

 

[Anonymous1378]

Try this?

Book6
	A	B	C
2	red	A	1
3	red	B	2
4	blue	A	1
5	blue	B	2
6	blue	B	2
7	blue	B	2
8	blue	C	3
9	yellow	A	1
10	yellow	B	2
11	yellow	C	3
12	yellow	D	4
13	yellow	D	4
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=XMATCH(A2&B2,UNIQUE(FILTER($A$2:$A$13&$B$2:$B$13,$A$2:$A$13=A2)))

 

[Engine1]

Hi *pr5,
You can make cell C2 = 1 and put this formula in cell C3: =IF(A2<>A3, 1, C2 + IF(B2<>B3, 1, 0)) (Copy down)
The first if allow to reset the count to 1 in the column C whenever a new series start in column A.
Following the same process, if the series in column A continue, the second if allow to add 1 to the previous value in column C whenever the column B starts a new series.
Hope this can help.

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

I think this fairly simple one also does what you want.

23 08 17.xlsm
	A	B	C
1	color	selection	result
2	red	A	1
3	red	B	2
4	blue	A	1
5	blue	B	2
6	blue	B	2
7	blue	B	2
8	blue	C	3
9	yellow	A	1
10	yellow	B	2
11	yellow	C	3
12	yellow	D	4
13	yellow	D	4
pr50

Cell Formulas
Range	Formula
C2:C13	C2	=IF(A2=A1,C1+(B1<>B2),1)