Generate Row Numbers by Group in Excel with Formulas

Formulas & functions · Updated November 21, 2026

To generate a row number that restarts for each group in Excel, put group names in A2:A100 and enter =COUNTIF($A$2:A2,A2) in B2, then fill down. Each occurrence of a group is numbered in the order it appears, even when other groups occur between its rows; use a different formula if you want numbering to restart only when a consecutive block changes.

What “row number by group” means

A group-based row number counts records separately within each group rather than assigning one uninterrupted sequence to the whole list. For example, if a table contains departments and employees, the first employee in each department can be numbered 1, the next 2, and so on. The group might instead be a product, customer, project, or any other repeated category.

There are two common interpretations. You may want to count every occurrence of a group throughout the list, wherever those rows appear. Or you may want to restart the count only when the group label changes as you move down the worksheet. Those answers differ when data is not sorted: an East row appearing after a West row is either East number 2 or the start of a new block numbered 1. Choose the behavior that matches what the numbers are meant to represent.

Use COUNTIF for a count across the whole list

Suppose column A contains a group label in each row and column B will contain the group-specific sequence. With group labels in A2:A100 and the first result in B2, enter =COUNTIF($A$2:A2,A2) in B2 and copy it down. The first range endpoint is fixed at row 2, while the second endpoint expands as the formula is copied. Excel therefore counts the current label from the first data row through the current row.

For example, if A2:A7 contains East, West, East, East, West, North, the formula returns 1, 1, 2, 3, 2, 1. East reaches 2 on its second appearance and 3 on its third, even though West rows sit between them. This method is usually the simplest option for a list that may be unsorted.

In Microsoft 365 for Windows, select B2, type the formula, and press Enter. Drag the fill handle at the lower-right corner of the cell down the column, or copy B2 and paste into the remaining result cells. If the source is an Excel Table, entering the formula in the first result cell normally fills the calculated column for the table.

Leave blank group rows unnumbered

A plain COUNTIF formula can return 0 for a blank group cell. If blank rows should stay blank instead, add a check before counting. With group labels in A2:A100 and results beginning in B2, use =IF(A2="","",COUNTIF($A$2:A2,A2)) in B2 and fill down.

For a quick check, if A2 is empty, B2 displays an empty string rather than zero. If A2 contains East, the result is 1, assuming it is the first East in the range. The test handles a genuinely empty cell and a formula result that is an empty string. It does not remove spaces from labels: a cell containing one space is not the same as an empty cell, and it may be counted as its own group.

Restart only when a consecutive group changes

If each group occupies one uninterrupted block—for example, East appears in rows 2–4, West in rows 5–6, and East again in row 7—you may want each block to start at 1. This is different from counting all occurrences of East together. With group labels in A2:A100 and results in B, put =IF(A2="","",1) in B2. In B3, enter =IF(A3="","",IF(A3=A2,B2+1,1)) and fill B3 down.

For the sequence East, East, West, West, East in A2:A6, the results are 1, 2, 1, 2, 1. Each row compares its group with the row immediately above: a match adds one to the previous result, while a change starts at 1. The first-row formula is separate so that the header above the data cannot interfere with the calculation.

This approach relies on groups being in consecutive blocks. If the rows are rearranged, the numbering changes with them. A blank group row is left blank, and the next nonblank row starts a new sequence because its label differs from the blank cell above. If you want a blank row to preserve a sequence or treat separated blocks as one group, use a method that explicitly matches that intended rule.

Count by more than one field

A group can be defined by a combination of fields. For instance, rows may need separate numbering for each department and each job title. With department in A2:A100, title in B2:B100, and the result in C2, enter =COUNTIFS($A$2:A2,A2,$B$2:B2,B2) in C2 and fill down. This counts rows whose department and title both match the current row, from the first data row through the current row.

If the first three records are Sales / Analyst, Sales / Manager, and Sales / Analyst, the results are 1, 1, 2. The two Analyst rows belong to the same combined group, while Manager has its own count. COUNTIFS is also useful when a group is defined by a region and a category, or by other parallel columns. Keep the expanding ranges aligned so that each criterion range covers the same rows.

For a consecutive-block sequence based on multiple fields, the comparison must check every field that defines the group. A change in either department or title should start a new count. Before adapting the single-column method, decide whether the desired rule is “same combination anywhere in the list” or “same combination as the immediately preceding row.”

Choose a formula based on the data

The right formula depends on what the sequence means, not just on how the worksheet looks. Use COUNTIF when a label’s number should continue across the entire list, and use the previous-row comparison when each sorted block should restart. COUNTIFS extends the first approach to groups defined by multiple columns.

  • Unsorted records, same group counted together: use COUNTIF or COUNTIFS with a range that expands down the list.
  • Sorted records, restart at each block: compare the current group with the preceding row.
  • Blank category cells: wrap the count in an IF check if blank rows should remain visually empty.
  • Numbering must not change after sorting: store the assigned values as values, or use a permanent record identifier rather than a formula tied to row order.

These formulas calculate a position in the current worksheet order; they do not create an immutable ID. Sorting the source changes which matching rows come first and can therefore change the numbers. That is useful when numbering should reflect the displayed order, but it may be unsuitable for a reference number used by another system.

Fill formulas reliably and check the data

Before copying a formula far down a sheet, verify the first few results against a small example. The dollar signs in $A$2:A2 are important: they keep the starting cell fixed while allowing the ending row to move. If the entire range were fixed as $A$2:$A$100, every row could count all matching labels in the full range rather than only rows up to the current one.

Check that group labels are consistent. “North” and “North ” with a trailing space can behave as different criteria, and spelling variations such as “Customer A” and “Cust A” also create separate groups. COUNTIF matching is not case-sensitive, so “east” and “East” are generally treated as the same text criterion. If capitalization is intended to define distinct groups, these formulas alone do not provide case-sensitive grouping.

When you add records, consider converting the range to a Table using Insert > Table. Tables make it easier to maintain a growing list and can extend a calculated column as new rows are added. If new rows are outside the formula’s copied range in an ordinary worksheet, fill the formula into those rows too. For a workbook where formulas are not updating after edits, check that calculation is set to Automatic under Formulas > Calculation Options.

Common problems and misconceptions

  • The count appears to restart unexpectedly: the formula may be designed for consecutive blocks, or group labels may differ because of extra spaces or inconsistent text.
  • Every row shows 1: check that the first endpoint is fixed but the second endpoint changes as the formula is filled down. Also confirm that the group values really match.
  • Blank rows show 0: use the IF-wrapped version if you want blanks to display as empty.
  • Numbers change after sorting: this is normal for formulas based on row order. The formula recalculates according to the new order.
  • Formula separators cause an error: Excel’s argument separator depends on regional settings. If your Excel expects semicolons, replace the commas with semicolons when entering the formula.

A related misconception is that a grouped row number is automatically a unique identifier. It is not: every group has a row numbered 1, and sorting can change which record receives each number. If another sheet needs to refer to a specific record permanently, use a unique ID column and keep the group sequence as a separate display or analysis field.

Frequently Asked Questions

How do I number rows 1, 2, 3 within each group in Excel?
With group labels in A2:A100 and output in B2, enter =COUNTIF($A$2:A2,A2) in B2 and fill down. It counts each label from the first data row through the current row.

How do I restart numbering when the group name changes?
Enter 1 for the first data row, then compare each later label with the one above it. With labels in A2:A100 and output in B, use =IF(A2="","",1) in B2 and =IF(A3="","",IF(A3=A2,B2+1,1)) in B3, then fill the second formula down.

Can I number groups using two columns?
Yes. With the two group fields in A and B and the first result in C2, use =COUNTIFS($A$2:A2,A2,$B$2:B2,B2). Each distinct combination gets its own sequence.

Will the group numbers stay the same after sorting?
Not necessarily. These formulas use the current row order, so sorting can change which matching record is first. If the assigned number must remain fixed, copy the results and use Paste Special > Values, or use a separate permanent ID.

Does the formula work in Excel for Mac or Excel for the web?
COUNTIF, COUNTIFS, and IF are available in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. The formulas are the same; interface details for filling or converting ranges to Tables may differ slightly.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.