Number rows for each unique value using arrayformula in google sheet

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

1
google-sheetsgoogle-sheets-formulaarray-formulas
Could you post the formula you have tried? – ADW
I tried something like =ArrayFormula(countif(A1: indirect("A"&row(A1:A7)),"="&indirect("A"&row(A1:A7)))) but I really have no idea – For Memo
Without an ARRAYFORMULA a simple COUNTIF =COUNTIF($A$2:$A$8,"="&$A2) in B2 and drag down – varontron
I'm trying to automate the sheet so that's why I'm asking. – For Memo

1 Answers

1
votes

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.