Formula to count number of matching contiguous cells?

[alexcr]

Let's say there are 10 cells in a row...

Apple, Apple, Banana, Banana, Banana, Apple, Orange, Orange, Orange, Orange

In the next row, you want to automatically calculate the number of *contiguous* cells that match the cell above, a formula which should produce...

2, 2, 3, 3, 3, 1, 4, 4, 4, 4

Note that this would be different than calculating the total number of cells in the row that match the cell above. For example, even though "Apple" appears three times in total, in the first contiguous block of "Apple", it appears twice, and in the second contiguous block of "Apple", it only appears once.

What would be the best formula to accomplish this?a

 

[Peter_SSs]

Not exactly sure of our layout, but does this help?

22 07 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		Apple	Apple	Banana	Banana	Banana	Apple	Orange	Orange	Orange	Orange
2		2	2	3	3	3	1	4	4	4	4
3
Sheet5

Cell Formulas
Range	Formula
B2:K2	B2	=IF(B1=A1,A2,MATCH(TRUE,B1:K1<>C1:L1,0))

 

[alexcr]

Thank you, Peter! Your formula seems to work well. While looking at it, I came up with another approach, albeit a two-row one, that also seems to work.

In cell B2, I entered the formula =IF(A1=B1,A2+1,1). I then copied this down its row.

In cell B3, I entered the formula =IF(B1=C1,C3,B2). I then copied this down its row.

 

[Peter_SSs]

You're welcome. Glad to help.

Yes, your method will also work if you don't mind using the helper row.