Countif as a dynamic array

Question

Hi

(relates to Numbering an ID with some specific criteria)

Sample

Range A2:A11 aka SmallRange consists of non-unique IDs desordered

Goal

With formula only, generate a countif array. Expected result in green columns

Options so far

B2: =COUNTIF(OFFSET(A2,,,SEQUENCE(ROWS(SmallRange))),SmallRange) 

C2: =BYROW(LargeRange,LAMBDA(Id,COUNTIF(A2:Id,Id))) 

D2: =MAP(LargeRange,LAMBDA(Id,COUNTIF(A2:Id,Id)))

Unfortunately none seem to scale. In this file LargeRange consists of 100k rows and 100 unique IDs for checking and to dev. other options...

My current LAMBDA based solution that performs (I think) "decently" considering that range is avail. here

Thanks

Answer 1

Hi. Just 2 cents. My opinion is that CountIf Is not the best function to use for a few reasons.

First, you are counting a large range multiple times.

Second, it doesn't work well within functions (not ref a worksheet).

There are other functions that can do your large range almost immediately.

Answer 2

Hi Rand

Very much appreciate your 2 cents :) Would you mind sharing your Tally.ByOccur function or a link to your workbook?

Thanks

Hi Yuhao Li - MSFT

Not sure your reply has something to do with this issue. If this is a mistake would you mind deleting it please?

Answer 3

Already deleted