How to count unique values in Excel
A free Office suite fully compatible with Microsoft Office
There is no single function can count unique values in WPS Spreadsheet now, so we try to make it in anther way.
Here we take a simple table as an example. It is a table of consumption records in a canteen.
To make it more clear, some duplicate records are highlighted in different colors.
Each ID card number corresponds to only one employees. Employees can enjoy different dishes by multiple payments when having lunch.
This table records the number of payments. Now we want to work out how many employees have lunch today.
Here are the steps.
1. Click the Data tab > Manage Duplicates > Fetch Unique Values. Then a dialog pops up.
2. In the List Range, select column A and B (refers to ID card number and name). Check Retain Only One and click Export to new Sheet. Then the unique ID card numbers and the corresponding employees’ names are fetched.
3. To work out the total number, we can add a new column and input “1” in the cell A2 and drag it to the last one. Then we can know the total number of employees who have lunch today is “30”.
For all, easy your work with WPS office suite. Free download WPS Office for PC, Mobile, Windows, Mac, Android, iOS online.
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. Check if value is in list in Excel (3 easy methods)
- 3. How to Copy File Names in Excel from a Folder?
- 4. How to add text to beginning or end of all cells in Excel
- 5. How to color cell based on value in Excel?
- 6. How to compare two excel sheets and highlight differences