You can use the following formula to generate sequence numbers by group in Excel:
=COUNTIF($A$2:A2,A2)
This particular formula assumes that the first unique group name is in cell A2 and all remaining groups are also in column A.
The following example shows how to use this formula in practice.
Example: Generate Sequence Numbers by Group in Excel
Suppose we have the following dataset in Excel that contains information about points scored by basketball players on various teams:
Suppose we would like to add a new column that contains sequence numbers by team.
For example:
- The first row with a team name of “Mavs” should have a value of 1.
- The second row with a team name of “Mavs” should have a value of 2.
- The third row with a team name of “Mavs” should have a value of 3.
And so on.
The sequence numbers should automatically start over when a new team name is encountered.
We can type the following formula into cell C2 to accomplish this:
=COUNTIF($A$2:A2,A2)
We can then drag and fill this formula down to each remaining cell in column C:
Column C now contains sequence numbers by team.
It’s worth noting that this formula also works even if the team names are not in consecutive order.
For example, suppose we have the following dataset in which the team names are scrambled:
We can still use the following formula in cell C2 to generate sequence numbers by team name:
=COUNTIF($A$2:A2,A2)
The following screenshot shows how to use this formula in practice:
Notice that the sequence numbers are still correct.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Autofill Values from Another Sheet
Excel: How to AutoFill Dates
Excel: Generate Random Number Between Range
