How to Count Users with at Least Two Certifications in WPS Office Spreadsheets
Question details
The user needs a formula to count users in a certification table who have a selected certification and at least two total certification records, while excluding users with only one certification record.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Analyzing a certification table that contains multiple rows per user and producing a single count based on a selected certification and a minimum total certification count.
- Observed behavior
- The goal is to return an accurate count of qualifying users without counting users who have only one total certification record.
Confirm that your WPS Office version supports dynamic array functions such as LET, UNIQUE, FILTER, and XMATCH. Format the certification data as an Excel Table or adjust the structured references in the formula to match your workbook range.
Use a Dynamic Array Formula to Count Qualifying Users
Best when your WPS Office version supports LET, UNIQUE, FILTER, and XMATCH; it produces the result in one cell and updates automatically.
This formula identifies unique users, counts each user's total certification records, keeps users with more than one record, and then counts rows for the selected certification among those users.
Open your certification workbook in WPS Office Spreadsheets and click the cell where the count should appear, for example E3.
Type =LET(UserNames,UNIQUE(Table1[User Name]),CertifCount,COUNTIF(Table1[User Name],UserNames),MoreOneCertif,FILTER(UserNames,CertifCount>1),GoodUsers,ISNUMBER(XMATCH(Table1[User Name],MoreOneCertif)),COUNTA(FILTER(Table1[User Name],(Table1[Core Certification]=D3)*GoodUsers))) and press Enter.
If your table or column names differ, replace Table1, User Name, and Core Certification with the exact names from your workbook, and change D3 to the cell containing the selected certification.
Filter the table manually for the selected certification and confirm that only users with more than one total certification row are counted.
Use Helper Columns for a Non-Dynamic Array Workaround
Use this if your WPS Office version does not support LET or FILTER, or if you prefer a step-by-step helper-column approach.
Count Qualifying Users in WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel-compatible dynamic array formulas, so you can count users with a selected certification and at least two total certification records without switching tools.
- 1. Download and install WPS Office: Go to the WPS Office download page, get the free installer, and complete the installation on your computer.
- 2. Open your certification workbook: Launch WPS Office Spreadsheets and open the Excel file that contains the certification table.
- 3. Enter the counting formula: Click the result cell, paste the LET/UNIQUE/FILTER/XMATCH formula, and press Enter.
- 4. Adjust references and verify: Replace table and column names to match your workbook, change D3 to the selected certification cell, and verify the returned count.
Frequently Asked Questions
Does this formula work in WPS Office?
Yes, if your WPS Office version supports dynamic array functions LET, UNIQUE, FILTER, and XMATCH. Open the workbook in WPS Office Spreadsheets and use the same formula syntax as Excel 2021 or Microsoft 365.
What should I do if WPS Office does not recognize LET or FILTER?
Update WPS Office to a current version, or use the helper-column approach with COUNTIF and a manually created unique user list.
How do I adjust the formula if my table is named differently?
Replace Table1, User Name, and Core Certification with your actual Excel Table name and column headers. If you are not using a table, replace table references with normal ranges such as $A$2:$A$100.
Can I count a certification typed directly in the formula instead of D3?
Yes, replace D3 in the formula with text in double quotes, for example "Microsoft Certified".
Why does my formula return 0?
Check that the certification value matches exactly, that users actually have more than one total certification record, and that table and column names are spelled correctly.
