How can I count repeats in a a column but reset count on non-repeats, in an array formula

Question

I have data like this where there's just two words I need to check at any time. I cant figure out how to count the duplicates but break the counting if there is another value inbetween.

Column A	Desired Result
Apple	1
Apple	2
Apple	3
Pears	1
Pears	2
	(No match value because empty cell - resets count)
Pears	1
Pears	2
Apple	1
Pears	2

=ArrayFormula(iferror(SORT(ROW(D3:D),SORT(ROW(D3:D),D3:D,1),1)-MATCH(D3:D,SORT(D3:D),0)-ROW()+1))

This counts as I expect, but doesnt break the chain and doesnt let me specify the words though that may not be a problem..

TheMaster · Accepted Answer · 2024-12-18 03:09:22Z

1

Running sums can usually be done by SCAN:

=SCAN(1,A3:A12,LAMBDA(a,c,IF(c=OFFSET(c,-1,0),a+1,1)))

Here, we just add 1 to the accumulator, IF current value is equal to previous row's value(OFFSET by -1 row)

answered Dec 18, 2024 at 3:09

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robin Dec 18, 2024 at 9:12

This is exactly what I needed, thanks! Scan and lambda are new to me, I will need to read into those. I felt like offset was key as well but couldnt make it work properly with an array formula, but it seems like scan/lambda makes this an array by itself. Thank you so much!

mankowitz · Accepted Answer · 2024-12-18 02:44:04Z

1

I'm not sure what the best way to do draw this on SO, but basically, you want to compare adjacent cells and increment if same and reset to 1 if not, right?

A1: "Apple"          B1: 1
A2: "Apple"          B2: =if(A2=A1, B1+1, 1)
A3: "Apple"          B3: =if(A3=A2, B2+1, 1)
(and so on)

answered Dec 18, 2024 at 2:44

mankowitz

2,1151 gold badge19 silver badges45 bronze badges

1 Comment

Robin Dec 18, 2024 at 9:09

I've accepted the above more complex answer but I struggled with this for so long and your simpler formula is absolutely working as well if I make it into an array and just add a helper column for numbers. Thank you! =ArrayFormula(IF(C3:C=1,C3:C,If(D3:D=D2:D,O2:O+1,1)))

Collectives™ on Stack Overflow

How can I count repeats in a a column but reset count on non-repeats, in an array formula

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related