Formula Errors

How to Create an Excel Formula to Restart a Sequence for Each Item

Muhammad Talha Oct 8, 2026 868 views

Question details

The user needs to generate a running sequence in a specific column that automatically restarts at 1 for every new or repeated item found in another column.

How to Restart a Sequence for Each Item in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating an itemized running count or sequence for categories in a dataset, ensuring the generated sequence is retained even when exporting the data to CSV format.
Observed behavior: A dynamic formula is required to accurately count occurrences sequentially, resetting the count whenever a different item is encountered in the reference column.

Before you start

Ensure your dataset is organized and determine the starting row for your data. Sorting your data by the item column is recommended if you want grouped items to have a continuous sequence, though the formula works for scattered duplicates as well.

Solution 1Recommended

Use the COUNTIF Function with an Expanding Range

The most efficient way to create a resetting sequence is by using the COUNTIF function with a mixed reference, creating an expanding range that counts items as the formula is dragged down.

This method relies on locking the starting cell of the range while allowing the end of the range to expand. This tells Excel to only count the occurrences of an item up to the current row.

Select the starting cell

Click on the first cell in the column where you want the sequence to begin (for example, cell D2).

Enter the expanding COUNTIF formula

Type =COUNTIF($A$2:A2, A2) into the formula bar. The $A$2 locks the starting point of the range, while A2 allows the end of the range to dynamically expand as you copy it.

Apply the formula to the entire column

Press Enter to confirm the formula. Then, click and drag the fill handle (the small square at the bottom-right of the cell) down to apply the sequence formula to the rest of your data rows.

Use the COUNTIF Function with an Expanding Range

CSV Export Compatibility: Since this formula calculates standard numerical values, saving or exporting your final worksheet to CSV format will retain the generated sequence numbers perfectly.

Advanced Spreadsheet Tool

Easily Manage Data Sequences in WPS Spreadsheet

WPS Spreadsheet provides robust formula support, including advanced functions like COUNTIF, to help you organize, sequence, and manage your data effortlessly.

1. Open your data: Launch WPS Spreadsheet and open the workbook containing your item list.
2. Input the sequence formula: Select the target cell in your sequence column and enter =COUNTIF($A$2:A2, A2).
3. Fill down the column: Double-click the fill handle at the bottom-right corner of the cell to instantly apply the sequence calculation to all your data rows.

Fully compatible with Microsoft Excel formulas and file formatsLightweight and fast data processing for large datasetsFree and intuitive interface for seamless data management

microsoft office alternative - wps office

Frequently Asked Questions

Why is my sequence not resetting for new items?

Ensure you have locked the first reference in the COUNTIF range using absolute referencing (e.g., $A$2). If you forget the dollar signs, the range shifts entirely, and the formula will simply return 1 for every row.

Can I restart a sequence based on changes in two columns instead of one?

Yes, you can use the COUNTIFS function for multiple criteria. For example, typing =COUNTIFS($A$2:A2, A2, $B$2:B2, B2) will restart the sequence only when the combination of both column A and column B changes.

Does this sequence formula work correctly if there are blank rows?

The formula will continue to count items correctly, but if the item cell itself is blank, it will count the blank cells as a separate item category. To ignore blanks, you can wrap the formula in an IF statement, like =IF(A2="", "", COUNTIF($A$2:A2, A2)).

Excel & Data Problems