Count Duplicate Values Only Once in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on December 26, 2022

This tutorial demonstrates how to count duplicate values only once in Excel and Google Sheets.


countduplicates initial


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.

  1. Click in the list of cells that contain the duplicate values, and then, in the Ribbon, select Data > Sort & Filter > Advanced.


countduplicates advanced


  1. Make sure Copy to another location is selected.


countduplicates copy to another location


  1. 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.


countduplicates unique


The unique records are extracted from the list.


countduplicates filtered


The last step is to use the ROWS Function to count how many unique values are in the list.

  1. In a blank cell underneath the filtered values, type in the following formula:


countduplicates countrows


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.



countduplicates gs count unique

AI Formula Generator

Try for Free

See all How-To Articles