google-sheetsenumerateranking-functions

How to enumerate categories with formula in Google Sheets

I'm struggling to get solution for my simple task in Google Sheets: I want to use formula to enumerate categorical data from 1 to number of entries in category, then starting from 1 for next category etc. Data is already sorted

I've tried SCAN function like =SCAN(0; A:A; LAMBDA(counter; current; IF(INDEX(A:A; ROW()-1)=current); counter+1;1))) but problem is counter doesnt restart from 1 from the next category.

result should be like this:

A 1 A 2 A 3 B 1 B 2 C 1 C 2 C 3

Solution

  • You can try:

    =MAP(A1:A,LAMBDA(_,IF(_="",,COUNTIF(A1:_,_))))

    Without LAMBDA:

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