WPS Spreadsheet: Count Unique Serial Numbers by Date and Team
Question details
The user needs to count distinct serial numbers that match a selected date, the receiving team YELLOWS, and an employee team of YELLOW, RED, or GREEN.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains serial numbers, work dates, receiving teams, and employee teams. The user wants a filtered count of unique serial numbers.
- Observed behavior
- The required result is a single count of unique serial numbers that satisfy all date and team conditions.
Confirm which columns contain serial numbers, dates, receiving teams, and employee teams, and place the target date in a separate cell such as F2. Check that the date values are stored as real dates rather than text.
Use a dynamic-array formula to count matching unique serial numbers
Use this method in current WPS Spreadsheet versions that support LET, FILTER, UNIQUE, and XMATCH.
The formula filters serial numbers by the selected date, requires the receiving team to be YELLOWS, and accepts YELLOW, RED, or GREEN as the employee team. UNIQUE removes repeated serial numbers before ROWS counts the remaining values.
Open the worksheet in WPS Spreadsheet and click the cell where you want the unique serial number count to appear.
Enter =LET(d,F2,ROWS(UNIQUE(FILTER(A2:A10000,(L2:L10000=d)*(O2:O10000="YELLOWS")*ISNUMBER(XMATCH(P2:P10000,{"YELLOW","RED","GREEN"})))))) and press Enter.
Replace F2 with the selected-date cell and adjust A2:A10000, L2:L10000, O2:O10000, and P2:P10000 to match the serial number, date, receiving team, and employee team columns.
Change the date in the selected-date cell and confirm that the count updates for records received by YELLOWS and handled by YELLOW, RED, or GREEN.
Return the matching unique serial numbers before counting them
Use this diagnostic version when you want to review the serial number list and verify that the filters are correct.
Count filtered unique serial numbers directly in WPS Spreadsheet
WPS Spreadsheet can calculate a distinct serial number count with LET, FILTER, UNIQUE, and XMATCH. Enter the formula in a result cell, update the ranges for your worksheet, and change the date selector to refresh the result.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the serial number records.
- 2. Prepare the date selector: Enter the target date in a cell such as F2 and verify that the record date column uses the same date format.
- 3. Insert the formula: Click a blank result cell and enter the LET, FILTER, UNIQUE, and XMATCH counting formula.
- 4. Update the ranges: Replace the example column ranges with the actual serial number, date, receiving team, and employee team ranges in your worksheet.
Frequently Asked Questions
Which columns should be changed in the formula?
A2:A10000 should reference serial numbers, L2:L10000 the work date, O2:O10000 the receiving team, and P2:P10000 the employee team. Change F2 to the cell containing the selected date.
How can I count only records received by YELLOWS?
Keep the receiving-team condition as O2:O10000="YELLOWS". This excludes records assigned to other receiving teams.
How does the formula include three employee teams?
XMATCH checks the employee-team range against {"YELLOW","RED","GREEN"}; ISNUMBER converts matching results into TRUE values used by FILTER.
What should I do if the date column includes times?
Exact equality may fail when date cells include time values. Use a start date and next-day boundary, such as date>=start and date<start+1, to include every time on the selected date.
Can I view the serial numbers instead of only the count?
Yes. Use the UNIQUE and FILTER formula without ROWS: =LET(d,F2,UNIQUE(FILTER(A2:A10000,(L2:L10000=d)*(O2:O10000="YELLOWS")*ISNUMBER(XMATCH(P2:P10000,{"YELLOW","RED","GREEN"})))))
