How to count unique values in Excel

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.