CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
For Memo
For Memo

Reputation: 31

Number rows for each unique value using arrayformula in google sheet

A    B
DOG  1
DOG  2
DOG  3
DOG  4
CAT  1
CAT  2
BIRD 1

B1 is where the formula goes.

I'm trying to use arrayformula to number each row that contains the same value in column A. The number should change when the value changes. I was trying to use countif and indirect but it seems indirect doesn't work with arrayformula.

Upvotes: 1

Views: 770

Answers (1)

MattKing
MattKing

Reputation: 7773

the way to do this type of cumulative count is with a countifs() using ROW() as a second parameter. So in B1, you would put:

=ARRAYFORMULA(IF(A:A="",,COUNTIFS(A:A,A:A,ROW(A:A),"<="&ROW(A:A)))

hope this helps.

Upvotes: 2

Related Questions