Formula not working when parsing set a dynamic range in Google spreadsheet formula ARRAYFORMULA

Question

I am using ARRAYFORMULA in Google spreadsheet by parsing a dynamic range to it. However, it seems having conflict with the formula, which the return is not as expected.

Table

Column P	Expected Result
A	001
A	002
B	001
A	003

=arrayformula(TEXT(countifs('RAW'!$P$2:P2,'KAON RAW'!P2:P),"000"))

The reason I parse "'RAW'!$P$2:P2" is because to restrict the range up until the current row. This will keep the last row always the biggest number of the repeated value. The Column P will add records sometimes.

However, this dynamic range seems having conflict with ARRAYFORMULA.

Can someone helps? Or any alternative because the file is not always opening or active.

The above script generate this unexpected result

Answer 1

Use map() with countif(), like this:

=map(P2:P5, lambda(cell, 
  text(countif(P2:cell, cell), "000") 
))

To handle all rows down to the bottom, and show blanks on rows where there is no data, add if():

=map(P2:P, lambda(cell, 
  if(len(cell), 
    text(countif(P2:cell, cell), "000"), 
    iferror(ø) 
  ) 
))

See map() and countif().