google-sheetsgoogle-sheets-formulacumulative-sum

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

Solution

  • 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)