CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulacumulative-sum
Robin
Robin

Reputation: 13

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

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..

Upvotes: 1

Views: 66

Answers (2)

TheMaster
TheMaster

Reputation: 50383

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)

Upvotes: 1

mankowitz
mankowitz

Reputation: 2031

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)

Upvotes: 1

Related Questions