Count unique and distinct values in Excel with formula or pivot table

This tutorial shows you how to count unique and distinct values in Excel using formulas and PivotTables. We'll cover counting unique names, text, numbers, and case-sensitive values, as well as handling blank cells.

The key difference between "unique" and "distinct" is that "unique" values appear only once, while "distinct" values include the first occurrence of duplicates.

Counting Unique Values with Formulas:

A common method uses SUM, IF, and COUNTIF:

=SUM(IF(COUNTIF(range,range)=1,1,0)) (Array formula – enter with Ctrl Shift Enter)

This formula counts unique values in a specified range. For example, =SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0)) counts unique names in cells A2 to A10.

Variations for Specific Data Types:

• Unique Text: Add ISTEXT: =SUM(IF(ISTEXT(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))
• Unique Numbers: Add ISNUMBER: =SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))
• Case-Sensitive Unique: Use a helper column with EXACT and COUNTIF. For example: =IF(SUM((--EXACT($A$2:$A$10,A2)))=1,"Unique","Dupe") in the helper column, then =COUNTIF(helper_column,"Unique")

Counting Distinct Values with Formulas:

To count distinct values (unique plus the first occurrence of duplicates), use:

=SUM(1/COUNTIF(range,range)) (Array formula)

or

=SUMPRODUCT(1/COUNTIF(range,range)) (Regular formula)

Variations for Distinct Values:

Similar variations using ISTEXT, ISNUMBER, and helper columns for case sensitivity can be applied to count distinct values of specific data types, similar to the unique value counting methods. Remember to handle blank cells with an IF statement to avoid errors.

Counting Unique/Distinct Rows:

Use COUNTIFS to count unique or distinct rows based on multiple columns:

• Unique Rows: =SUM(IF(COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10)=1,1,0))
• Distinct Rows: =SUM(1/COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10))

Automatic Distinct Count in PivotTables:

Excel 2013 and later versions offer an automatic "Distinct Count" option in PivotTables. Create a PivotTable, add your data to the Data Model, and choose "Distinct Count" in the Value Field Settings.

This tutorial provides various methods for efficiently counting unique and distinct values in Excel, catering to different data types and complexities. A sample workbook is available for download (link provided in original text).

The above is the detailed content of Count unique and distinct values in Excel with formula or pivot table. For more information, please follow other related articles on the PHP Chinese website!