WPS Spreadsheet: Count Unique MR Numbers for Each Permit
Question details
The user needs to count distinct MR numbers associated with each permit without using VBA.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains permit values and related MR numbers, and the user wants one unique-count result for each permit using a formula that can spill into a dynamic layout.
- Observed behavior
- The desired result is a spilled list showing the number of unique, nonblank MR numbers for every permit.
Confirm which range contains the permit list and which two columns contain the source permits and MR numbers. Make sure the output cells below the formula are empty so the dynamic results can spill.
Use BYROW, FILTER, and UNIQUE to count nonblank MR numbers
Use this formula when you have a permit list in A2:A5 and source permits and MR numbers in A8:A23 and B8:B23.
The formula evaluates each permit separately, filters its matching MR numbers, removes duplicates, excludes blank MR values, and returns one count per permit. The result spills down automatically.
If your data ranges differ, replace A2:A5, A8:A23, and B8:B23 with the corresponding ranges in your worksheet.
Open the WPS Spreadsheet workbook and click the cell where the first unique MR count should appear.
Enter =BYROW(A2:A5,LAMBDA(permit,IFERROR(ROWS(UNIQUE(FILTER(B8:B23,(A8:A23=permit)*(B8:B23<>"")))),0))) and press Enter.
Check the cells below the formula to see one unique MR-number count for each permit in A2:A5. Ensure the spill area is not blocked by existing content.
Apply the formula to a dynamic Excel-style table
Use this option when the source data is stored in a table and should expand automatically as new permit and MR-number rows are added.
Count Distinct Permit-Linked MR Numbers in WPS Spreadsheet
WPS Spreadsheet supports dynamic array formulas such as BYROW, FILTER, UNIQUE, and LET, allowing you to calculate distinct MR-number counts without VBA and keep the results updated as source data changes.
- 1. Open your spreadsheet: Launch WPS Office, open WPS Spreadsheet, and load the workbook containing the permit and MR-number data.
- 2. Prepare the output area: Select an empty cell beside or below the permit list and clear any cells where the formula results need to spill.
- 3. Enter the unique-count formula: Use =BYROW(A2:A5,LAMBDA(permit,IFERROR(ROWS(UNIQUE(FILTER(B8:B23,(A8:A23=permit)*(B8:B23<>"")))),0))) with your own permit and source-data ranges.
- 4. Check and extend the results: Press Enter, verify the spilled counts, and convert the source range to a table from Insert > Table if you want new rows to be included automatically.
Frequently Asked Questions
How do I count unique MR numbers for one permit only?
Use =IFERROR(ROWS(UNIQUE(FILTER(B8:B23,(A8:A23=E2)*(B8:B23<>"")))),0), replacing E2 with the cell containing the permit to check.
Why does my unique count return 1 when there is no matching MR number?
COUNTA can count the empty text returned by FILTER. Use the condition B8:B23<>"" and wrap the result in IFERROR, as in the recommended formula, to return 0 instead.
Can the formula count unique MR numbers for every permit at once?
Yes. BYROW evaluates the calculation for every value in the permit range, such as A2:A5, and spills one result per permit.
Will the formula update when I add rows to the source data?
Use a WPS Spreadsheet table and structured references such as Table1[Permit] and Table1[MR Number]. New rows entered directly below the table are included automatically.
Does this method require VBA or macros?
No. The calculation uses worksheet functions only: BYROW, LAMBDA, FILTER, UNIQUE, ROWS, and IFERROR.
