Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to number rows within each group in Excel and Google Sheets
Topic:Data cleaning & shapingExcel & Google Sheets
=COUNTIF($A$2:A2,A2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Group | Row_Num |
| Sales | |
| Sales | |
| HR |
=COUNTIF($A$2:A2,A2)→1
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Ensure your data has a group column (e.g., department names in column A) with rows sorted by group.
- 2In column B row 2, enter =COUNTIF($A$2:A2,A2) — the first reference is anchored ($A$2) while the second expands (A2) as you copy down.
- 3Copy the formula down column B — it counts how many times the current group value has appeared so far, giving each row its position within the group.
- 4Sort your data by the group column first for clean sequential numbering (otherwise the count resets whenever the same group reappears).
Tips
- If your data isn't sorted, the count will restart each time the group appears in a non-contiguous block — sort by group column first for sequential within-group numbers.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
How do I number rows within groups without sorting the data first?
Use =COUNTIFS($A$2:A2,A2) which has the same behavior — for truly unsorted data you need a helper sort or a more complex SUMPRODUCT formula.
Can I use this to restart numbering with each new month or date?
Yes — replace the group column reference with a month/year expression: =COUNTIFS($B$2:B2,B2) where B holds MONTH(date) or TEXT(date,"YYYY-MM").
More on Data cleaning & shaping
See all →How-to
How to merge two columns
Combine two columns into one with the & operator — perfect for joining first and last names into a full name.
How-toHow to create a sequential invoice number
Automatically generate sequential invoice numbers with a prefix and zero-padded counter using TEXT and ROW in Excel and Google Sheets.
How-toHow to remove blank rows
Delete empty rows quickly by filtering for blanks and deleting visible rows, or use Go To Special in Excel for a one-click approach.
How-toHow to transpose rows to columns
Flip rows and columns using the TRANSPOSE function or Paste Special to convert horizontal data to vertical and vice versa.
How-toHow to combine multiple columns into one
Merge values from several columns into one cell using the ampersand operator or CONCAT/TEXTJOIN for delimited output.
How-toHow to fill blank cells with a value
Replace empty cells with a default value using IF+ISBLANK, Go To Special fill-down, or a Power Query step for bulk data.