How to Count Unique Serial Numbers by Country and Code in WPS Office Spreadsheets
Question details
The user needs to return the number of distinct serial numbers for each Country and Code combination using Microsoft 365 dynamic array functions.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a worksheet with Country, Code, and Serial Number columns and needs a dynamic result column that shows the number of distinct serial numbers for each country-code combination.
- Observed behavior
- The user needs a formula that spills the unique counts for every row instead of manually filtering or counting each combination.
Make sure your Country, Code, and Serial Number values are in parallel columns with consistent ranges, and confirm that your WPS Office Spreadsheets version supports dynamic array functions such as FILTER, UNIQUE, MAP, and LAMBDA.
Use a MAP, LAMBDA, COUNTA, UNIQUE, and FILTER dynamic array formula
Use this when you want a single spilled formula that returns the distinct serial count for each row's Country and Code combination.
This formula checks each row in A2:A10 and B2:B10, passes the current country and code to the FILTER function, removes duplicate serial numbers with UNIQUE, and counts the remaining entries with COUNTA.
Click cell D2, or the first cell in the column where you want the unique-count results to appear.
Type =MAP(A2:A10,B2:B10,LAMBDA(country,code,COUNTA(UNIQUE(FILTER($C$2:$C$10,($A$2:$A$10=country)*($B$2:$B$10=code),""))))) and make sure the ranges match your actual data.
Press Enter. WPS Office Spreadsheets returns one unique serial count for each country and code row automatically.
Use a BYROW formula with TAKE and CHOOSECOLS as an alternative
Use this if you prefer a row-by-row LAMBDA approach that works in Microsoft 365, Excel 2021, Excel for the web, or compatible WPS Office Spreadsheets versions.
Count Unique Serial Numbers by Country and Code in WPS Office Spreadsheets
Use WPS Office Spreadsheets to count distinct serial numbers for each country and code combination with Excel-compatible dynamic array functions such as FILTER, UNIQUE, MAP, and LAMBDA. Enter the formula once and let it spill results automatically.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the .xlsx workbook that contains your Country, Code, and Serial Number columns.
- 2. Select the first result cell: Click cell D2, or the first cell in the empty result column where you want unique counts to appear.
- 3. Enter the formula: Type =MAP(A2:A10,B2:B10,LAMBDA(country,code,COUNTA(UNIQUE(FILTER($C$2:$C$10,($A$2:$A$10=country)*($B$2:$B$10=code),""))))) and adjust the ranges to match your data.
- 4. Press Enter and review: Press Enter to spill the unique-count results for all rows, then verify a few country-code combinations against the source data.
- 5. Save the workbook: Click the Save icon or press Ctrl+S to keep the calculated results.
Frequently Asked Questions
Does this formula work if my country or code values are in different columns?
Yes. Replace A2:A10 and B2:B10 with the actual ranges for Country and Code, and C2:C10 with the Serial Number range. The formula counts unique serial numbers based on the two criteria in each row.
Can I use this method in WPS Office Spreadsheets with .xlsx files?
Yes. WPS Office Spreadsheets supports Excel-compatible dynamic array formulas and opens .xlsx files, so you can enter the same formula in the first result cell.
What should I do if the formula returns 1 for rows with no matching data?
This can happen when FILTER returns an empty string and COUNTA counts it. You can wrap the formula with IFERROR or add a condition to return 0 when the first matching serial cell is blank.
Can I create a unique list of country-code combinations automatically?
Yes. Enter =UNIQUE(A2:B10) in a blank area to spill a distinct list of country-code pairs, then place the MAP formula next to that list to count serial numbers for each pair.
