I have a set of data, which the value might be repeated and I am attempting to count and mark the counting in sequence.

I am currently trying to create a list using arrayformula (which is a must for my application for some reason), which the list will return the number of times the value is repeated, at the same time indicates the # of the occurrence of that row.

Some thing like the below is what i wish to acc

DATA Desired
a 1 1st a
b 1 1st b
c 1 1st c
a 2 2nd a
b 2 2nd b
a 3 3rd a
b 3 3rd b
b 4 4th b

It is a must for me to use a arrayformula or something similar to achieve. Would appreciate any input. Thanks.

1 Answer 1


Try with;

=BYROW (A2:A,LAMBDA(each,IF(each="","",COUNTA (FILTER(A2:each,A2:each = each)))))
  • Thanks for the suggestion. I get the idea of the formula, but it doesn't work (which caught me curious as well). The output I get is instead all 1.
    – Charononon
    Jan 18, 2023 at 3:03
  • My bad, I missed the range in filter, I corrected in the post. You can also use COUNTIF inserted of COUNTA and FILTER:. =BYROW (A2:A,LAMBDA(each,IF(each="","",COUNTIF (A2:each,each))))
    – Martín
    Jan 18, 2023 at 3:21
  • Hi Martin. I have tested both solutions you mentioned and it works perfectly. Thanks a lot for your comment.
    – Charononon
    Jan 18, 2023 at 3:24
  • I'm glad it was useful!
    – Martín
    Jan 18, 2023 at 3:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.