How to Create a Running Count of Unique Values by Another Column in WPS Office Excel
Question details
User needs to create a running count of unique values in one column while restarting the count based on another column, and optionally mark only the first occurrence of each value.
- Product
- WPS Office Spreadsheets (Excel)
- Device & OS
- not provided
- Scenario
- Numbering repeated values within categories, customers, departments, or other groups without manually counting each entry.
- Observed behavior
- A formula is needed to identify the first occurrence of each value and produce a sequential count that restarts whenever the grouping value changes.
Open your workbook in WPS Office and ensure your data has a header row. Identify the grouping column, such as column A, and the value column, such as column B, and confirm where you want the first formula to start.
Create a Running Count with COUNTIFS Based on Another Column
Use this formula when you want a sequential count for each value that restarts when the grouping column changes.
The COUNTIFS function evaluates multiple conditions across expanding ranges. Locking the starting row with $ creates a running range from the first data row to the current row, so the count increases only when both the group and value match previous rows.
Click the cell where the running count should appear, such as C2, next to your first data row.
Type =COUNTIFS($A$2:A2,A2,$B$2:B2,B2) and press Enter. Replace A and B with your actual grouping and value columns.
Use the fill handle in the bottom-right corner of C2 and drag down through the remaining rows, or double-click the fill handle to copy the formula automatically.
Mark Only First Occurrences of Each Value Within a Group
Use this when you only need to flag unique first occurrences instead of showing a running count.
Create Running Unique Counts in WPS Office Spreadsheets
Use the COUNTIFS formula in WPS Office Spreadsheets to create a grouped running count of unique values. The same Excel-compatible formula marks the first occurrence of each item so you can analyze duplicates by category without manual counting.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the grouping column and value column.
- 2. Insert the formula in the first data row: Click the result cell next to the first data row and enter =COUNTIFS($A$2:A2,A2,$B$2:B2,B2).
- 3. Drag the formula down: Use the fill handle to copy the formula through all data rows.
- 4. Mark unique first occurrences: In another column, enter =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,1,0) and fill down to flag first occurrences.
- 5. Save in Excel format: Choose Menu > Save As and select Excel (.xlsx) to share the workbook with Microsoft Office users.
Frequently Asked Questions
Why does the COUNTIFS formula use a locked starting row with $A$2?
Locking the starting row with $A$2 makes the range expand from row 2 to the current row as you fill down, so WPS counts only the rows above and including the current row instead of the entire column.
Can I use this formula if my data does not start in row 2?
Yes. Replace A$2 and B$2 with your first data row, and enter the formula in the corresponding first result row. For example, if data starts at row 5, use =COUNTIFS($A$5:A5,A5,$B$5:B5,B5).
How do I create a count that does not restart by another column?
Remove the grouping condition and use =COUNTIF($B$2:B2,B2). This counts occurrences of the value in column B without restarting by category.
What is the difference between the running count and the unique flag formula?
The running count returns 1,2,3 for repeated values within a group. The unique flag formula wraps COUNTIFS in IF and returns 1 only for the first occurrence and 0 for all later occurrences.
Will this work when I open the file in Microsoft Excel?
Yes. COUNTIFS is supported in Excel-compatible formats, so formulas created in WPS Office Spreadsheets work when the file is saved as .xlsx and opened in Microsoft Excel.
