How to Count Unique Employee Names with a Positive Value in WPS Office Spreadsheets
Question details
The user needs a formula-compatible method to count employee names in column E and apply a uniqueness condition while checking that the related value in column G is greater than zero.
- Product
- WPS Office Spreadsheets (Excel 2016-compatible)
- Device & OS
- not provided
- Scenario
- The user is working with an employee worksheet that contains names in E6:E1991 and numeric values in G6:G1991 and wants a summarized count of records that satisfy both the unique-name condition and the positive-value condition.
- Observed behavior
- The existing counting approach may mix up exact-once names and distinct names with a qualifying value, so the returned total does not accurately represent the intended condition.
Open the workbook in WPS Office Spreadsheets and confirm employee names are in column E and numeric values are in column G, with data starting at row 6. If the file is in Excel 2016 format (.xlsx), WPS Office can open and edit it directly.
Count Names That Appear Exactly Once in Column E
Use this when a unique employee name means it occurs only once in E6:E1991, regardless of the value in column G.
This formula counts each name only when its total frequency in the selected range equals 1.
Click the cell where you want the unique-name count to appear.
Type =SUMPRODUCT(($E$6:$E$1991<>"")*(COUNTIF($E$6:$E$1991,$E$6:$E$1991&"")=1)).
Press Enter in WPS Office Spreadsheets. In Excel 2016, if the formula prompts for array entry, press Ctrl+Shift+Enter.
Count Distinct Employee Names That Have a Positive Value in Column G
Use this when an employee should be counted once if at least one of their rows has a value greater than zero in column G.
Count Employee Names That Are Unique and Have a Positive Value in Column G
Use this recommended method when the name must appear exactly once in column E and the related value in column G must be greater than zero on that same row.
Count Unique Employee Names with Positive Values in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same COUNTIF, COUNTIFS, and SUMPRODUCT formulas used in Excel 2016. You can open your existing .xlsx workbook, enter the array-compatible count formula, and get the required employee totals without changing your file format.
- 1. Install and open WPS Office: Download WPS Office from the official website, install it, and launch WPS Spreadsheets from the home screen.
- 2. Open the Excel 2016 workbook: Click Menu > Open and select the .xlsx employee workbook containing names in column E and values in column G.
- 3. Enter the counting formula: Select the result cell, enter the SUMPRODUCT/COUNTIFS formula for your uniqueness and positive-value condition, and press Enter.
- 4. Save the result: Click Menu > Save to keep the workbook in .xlsx format for continued use in both WPS Office and Excel 2016.
Frequently Asked Questions
How do I count only employee names that appear exactly once in column E?
Use =SUMPRODUCT(($E$6:$E$1991<>"")*(COUNTIF($E$6:$E$1991,$E$6:$E$1991&"")=1)). It counts a name only when its total frequency in E6:E1991 equals 1.
How can I count distinct employee names that have at least one positive value in column G?
Enter the COUNTIFS-based SUMPRODUCT formula =SUMPRODUCT(($E$6:$E$1991<>"")*(COUNTIFS($E$6:$E$1991,$E$6:$E$1991&"",$G$6:$G$1991,">0")>0)/COUNTIF($E$6:$E$1991,$E$6:$E$1991&"")). In Excel 2016, confirm with Ctrl+Shift+Enter when required.
Do these formulas work the same way in WPS Office Spreadsheets and Excel 2016?
Yes. WPS Office Spreadsheets supports COUNTIF, COUNTIFS, and SUMPRODUCT with Excel-compatible syntax, so you can edit the same .xlsx workbook in either application.
Why do I get #DIV/0! with a COUNTIFS-based SUMPRODUCT formula?
The error usually appears when the denominator evaluates to zero for blank cells or a name with no qualifying positive value. Use the version with COUNTIF as the denominator and keep the E6:E1991<>"" check in the formula.
Can I adjust the formula ranges if my data does not end at row 1991?
Yes. Replace $E$6:$E$1991 and $G$6:$G$1991 with your actual data ranges, keeping both the COUNTIF and COUNTIFS ranges consistent.
