CODEWITHSUNDEEP
excel
user3779804
user3779804

Reputation: 33

Is there an Excel function to dynamically count and number rows?

We are trying to find a way to automatically generate and insert a number count into a column (SORT #) in Excel, as seen in the image.

Each account (ACCT#) belongs to a branch (BRANCH #) and must be assigned a number based on how many accounts each branch has. For example, account 1006 is the 6th account that belongs to branch 51, therefore it is given a count (SORT #) of 6. Account 1015 belongs to branch 21, therefore it is given a SORT # of 1, and so on.

I tried experimenting with the COUNTIF functions (ex: =COUNTIF(B:B,51) ), but could not find a way to auto-fill the cells in the SORT # column. Flash Fill also did not work to generate the column.

enter image description here

Upvotes: 0

Views: 2318

Answers (1)

Dave
Dave

Reputation: 1643

Try using a mixed reference:

=COUNTIF($B$2:B2,B2)

As you fill down you should see the lookup range extending. This will count the instances of whatever is in column B from row 2 up to the row your formula is in.

Upvotes: 2

Related Questions