Count Duplicate Values Only Once in Excel & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to count duplicate values only once in Excel and Google Sheets.
Count Duplicate Values Once
You can extract unique values from a range of cells in Excel by using an advanced filter and the ROWS Function. By counting the unique values this generates, you are only counting the duplicate values once.
Steps 1–3 extract unique values by using Excel’s Advanced Filter.
- Click in the list of cells that contain the duplicate values, and then, in the Ribbon, select Data > Sort & Filter > Advanced.
- Make sure Copy to another location is selected.
- Then, select where to populate the new list of just unique values in the Copy to: box, and ensure that Unique records only is ticked.
The unique records are extracted from the list.
The last step is to use the ROWS Function to count how many unique values are in the list.
- In a blank cell underneath the filtered values, type in the following formula:
=ROWS(E3:E8)
Count Duplicate Values Once in Google Sheets
You cannot extract unique values in Google Sheets in the same way; advanced filter isn’t an option. You can, however, exclude duplicate values using formulas.
One formula you can use is COUNTUNIQUE, which counts the unique values in a list, to count duplicates only once.
=COUNTUNIQUE(B3:B11)
