How to Number Samples by Product and Date with COUNTIFS in WPS Office Spreadsheets
Question details
The user needs a formula-only method to number each product-and-sample-date group sequentially in WPS Spreadsheets, using COUNTIFS instead of VBA.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has product codes in one column and sample dates in another, and wants a sample number that restarts at 1 for each product on each date.
- Observed behavior
- There is currently no automatic sequence; the desired result is to see 1, 2, 3 for each matching product-and-date group.
Make sure product values are in column A and sample dates are in column B. The numbering column can start in D2, and the formula expects data to begin in row 2.
Create Sequential Sample Numbers with a COUNTIFS Formula
Use this solution when you need a formula-only sequence for each product on each sample date without VBA or helper columns.
The formula creates an expanding range from the first data row to the current row. COUNTIFS only counts rows where the product in column A and the date in column B both match the current row, so each matching row is counted once.
Click D2, or the first empty cell in the column where you want the sample sequence.
Type =COUNTIFS($A$2:A2,A2,$B$2:B2,B2) and press Enter.
Drag the fill handle from D2 down to the last data row so WPS Spreadsheets updates the formula for each row.
Number Samples by Product and Date in WPS Office Spreadsheets
Use the COUNTIFS function in WPS Office Spreadsheets to create a formula-only sequence that numbers each product-and-date combination from 1 upward without VBA.
- 1. Open your workbook in WPS Spreadsheets: Open the file containing product codes in column A and sample dates in column B.
- 2. Add a sample-number column: Click D1 and type a heading such as Sample Number.
- 3. Enter the COUNTIFS formula in D2: Type =COUNTIFS($A$2:A2,A2,$B$2:B2,B2) and press Enter.
- 4. Fill the formula down: Drag the fill handle from D2 through all data rows.
- 5. Check the resets: Verify the sequence starts at 1 for each new product and date combination.
Frequently Asked Questions
Can I create sequential sample numbers without using VBA in WPS Spreadsheets?
Yes. Use =COUNTIFS($A$2:A2,A2,$B$2:B2,B2) in the first data row and fill down. It counts matching product-and-date rows up to the current row.
Why does the formula use $A$2:A2 instead of A:A?
The mixed reference locks the starting row at 2 while the ending row expands as you fill down, so the count only includes rows from the top through the current row.
Does this COUNTIFS formula work if my data does not start in row 2?
Yes. Change the starting cell to your first data row, for example =COUNTIFS($A$5:A5,A5,$B$5:B5,B5).
Will this method work for more than one product on the same date?
Yes. COUNTIFS requires both product and date to match, so products are counted separately even when they appear on the same date.
Can I use this formula in Microsoft Excel too?
Yes. The COUNTIFS formula is compatible between WPS Spreadsheets and Excel, so the same approach works in both.
