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.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
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.