How to use arrayformula to indicate an occurrence in google sheets

Question

I'm trying to write an =arrayformula that finds the 1st, 2nd, 3rd, etc. occurrence of a value in another column. I can do this with a formula copied down all rows but I don't want to use this method, I need the arrayformula to simply always update the column because I need to constantly insert and delete rows and I don't want to have to keep copying the formula down. Thanks in advance.

Google sheet where I'm testing this.

Example formulas that I currently have to copy down..I want this to simply be an arrayformula instead:

values | occurance

aa | =if(A3<>"",countif($A$3:$A3,A3),"") | RESULT:1

2 | =if(A4<>"",countif($A$3:$A4,A4),"") | RESULT:1

aa | =if(A5<>"",countif($A$3:$A5,A5),"") | RESULT:2

5 | =if(A6<>"",countif($A$3:$A6,A6),"") | RESULT:1

5 | =if(A7<>"",countif($A$3:$A7,A7),"") | RESULT:2

4 | =if(A8<>"",countif($A$3:$A8,A8),"") | RESULT:1

Formulas that work but have to be copied down

SO suggested it and seemed appropriate in the event someone can offer excel example that would be applicable or similar enough to get me somewhere. — tompound

JPV JPV · Accepted Answer · 2015-10-17T19:10:44

Check out cell D3 where I used this formula:

=ArrayFormula(iferror(SORT(ROW(A3:A),SORT(ROW(A3:A),A3:A,1),1)-MATCH(A3:A,SORT(A3:A),0)-ROW()+2))

and see if that works for you ?

How to use arrayformula to indicate an occurrence in google sheets

1 Answers