Function Problems

How to Number Only the First Row of Each Item Group in Excel

Muhammad Talha Oct 20, 2026 868 views

Question details

The user wants to apply sequential numbering exclusively to the first row of multi-row item groups that are separated by blank rows.

How to Number Only the First Row of Each Item Group in Excel

Product: Excel
Device & OS: not provided
Scenario: Organizing descriptions where each new item spans multiple rows and is separated from the next item by a blank row. The goal is to generate a sequential item number beside the heading row only.
Observed behavior: A standard COUNTIF formula applies a number to every non-blank cell, causing continuation rows to be incorrectly numbered alongside the first heading row.

Before you start

Verify that your data is structured consistently, ensuring that each multi-row item group is separated by at least one completely blank row.

Solution 1Recommended

Use an IF and SUMPRODUCT Formula

This formula checks if the current cell contains data while the preceding cell is empty, applying a sequential number only at the start of a new item group.

A simple COUNTIF numbers every non-empty row. To number only the heading rows, the formula must uniquely identify a non-blank cell whose preceding cell is blank. By combining IF, AND, and SUMPRODUCT, Excel can evaluate whether a cell represents the actual start of a new group.

Select the starting cell

Click on the cell in your numbering column that corresponds to the first row of your data (for example, A2 if your descriptions are in column B starting at row 2).

Enter the logical formula

Type the following formula into the formula bar: =IF(AND(B2<>"",OR(ROW()=2,B1="")),SUMPRODUCT(--($B$2:B2<>""),--($B$1:B1="")),"")

Apply the formula

Press the Enter key to calculate the result for the first cell.

Fill down the column

Click and hold the small square at the bottom-right corner of the cell (the fill handle), and drag it down to the bottom of your dataset to number all groups.

Adjusting Row References: If your first data row is different from row 2, you must adjust the ROW()=2 segment and the range references ($B$2:B2 and $B$1:B1) to match your actual starting row.

Advanced Spreadsheet Functions

Easily Handle Complex Formulas with WPS Spreadsheet

WPS Office provides a highly capable, user-friendly spreadsheet application that perfectly executes complex array formulas and logical functions like SUMPRODUCT and IF. It operates exactly like Microsoft Excel, ensuring your formulas work without modification.

1. Open your document: Launch WPS Spreadsheet and open the document containing your grouped items.
2. Target the numbering column: Click on the top cell of the column where you want the sequential numbers to appear.
3. Paste the formula: Copy and paste the IF and SUMPRODUCT formula directly into the top formula bar.
4. Drag to apply: Use the fill handle to drag the formula down your column, automatically numbering the first row of every group.

100% compatibility with Microsoft Excel functions and .xlsx file formats.Powerful processing for array formulas and complex data validation.Lightweight application with a highly intuitive, familiar interface.Completely free to use for both personal and professional data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the COUNTIF function number every row instead of just the first?

The COUNTIF function simply counts all non-blank cells within a specified expanding range. It lacks the logic to evaluate whether a cell belongs to a new group or is merely a continuation row, resulting in every cell containing text receiving a number.

What if my item groups are not separated by blank rows?

If your groups are not separated by blank rows, the logic checking for previous blank cells (B1="") will fail. You would need to use a different identifier to mark the start of a group, such as a specific text pattern, a distinct character, or an auxiliary column.

Can I start the sequence from a number other than 1?

Yes. You can add a static integer to the SUMPRODUCT portion of the formula. For example, if you want your first group to be labeled as number 10, simply append +9 to the SUMPRODUCT calculation.

Excel & Data Problems