CODEWITHSUNDEEP
excelexcel-formulaexcel-2021
isaid-hi
isaid-hi

Reputation: 173

Count each time value appears, reset if value change, using dynamic array

Data

| label | seq |
| ----- | --- |
| a     |     |
| a     |     |
| a     |     |
| b     |     |
| b     |     |
| c     |     |
| c     |     |
| c     |     |

What to do

I want to count the number label appears in the seq column. The counter sould reset when label change. So the expected result would be like this:

| label | seq |
| ----- | --- |
| a     | 1   |
| a     | 2   |
| a     | 3   |
| b     | 1   |
| b     | 2   |
| c     | 1   |
| c     | 2   |
| c     | 3   |

So far

One of the works method is using COUNTIF. Assuming that table header start from A1, the formula in B2 would be COUNTIF($A$2:A2, A2) and copy all the way down.

Problem

Most efficient solutions in the community is as I mentioned above (to the best of my research), but I want the formula using dynamic array so i don't need to copy formula for each row. This is necessary for my automation workflow

Edit: I forgot to mention that I'm only using Excel 2021 and not Excel 365. So need more humble formula. Finger crossed.

Upvotes: 0

Views: 84

Answers (2)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

You could try using one of the following as well:

enter image description here

=MAP(A2:A9,LAMBDA(x,COUNTIF(A2:x,x)))

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152505

With SCAN we can iterate:

=SCAN(0,SEQUENCE(ROWS(A2:A10)),LAMBDA(_z,_y,COUNTIFS($A$2:INDEX(A2:A10,_y),INDEX(A2:A10,_y))))

enter image description here

With LET we can reduce the references:

=LET(
    rng,A2:A10,
    SCAN(0,SEQUENCE(ROWS(rng)),LAMBDA(_z,_y,COUNTIFS(INDEX(rng,1):INDEX(rng,_y),INDEX(rng,_y)))))

Upvotes: 2

Related Questions