Microsoft KB Archive/268001

= HOW TO: Count Unique Elements in a Cell Range in Excel =

PSS ID Number: 268001

Article Last Modified on 3/15/2004

-

The information in this article applies to:


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002
 * Microsoft Excel 2000
 * Microsoft Excel 97 for Windows
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 Macintosh Edition

-



This article was previously published under Q268001



IN THIS TASK

 * SUMMARY
 * ** Count Unique Number Elements
 * Count Unique Text and Number Elements
 * Count Unique Elements with Blank Cells
 * Sum of Unique Number Elements
 * REFERENCES



SUMMARY
This step-by-step article shows you how to use the FREQUENCY function in an array formula to count the unique elements in a cell range.

back to the top

Count Unique Number Elements
The following formula counts the number of unique values found in the range A1:A10 and does not count blank cells and text entries:

=SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1)) NOTE: The preceding formula, and those that follow, are array formulas. To enter a formula as an array, press CTRL+SHIFT+ENTER.

The FREQUENCY function returns a range of numbers. For the first occurrence of a specific value, this function returns a number equal to the number of occurrences of that value. For each occurrence of that same value after the first, this function returns a zero. Therefore, this function counts the number of unique values.

back to the top

Counting Unique Text and Number Elements
Because the FREQUENCY function works only with numbers, you must add a level to evaluate unique text entries (or mixed numbers and text), as in the following example:

=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1)) The preceding formula counts the number of unique text and numeric entries for A1:A10, where A1:A10 contains no blanks cells.

back to the top

Counting Unique Elements with Blank Cells
To count unique numeric and text elements in a range that includes blank cells, add an additional test:

  =SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),&quot;&quot;),      IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),&quot;&quot;))>0,1)) back to the top

Sum of Unique Number Elements
To sum unique values for a range of cells, substitute the final a1:a10 for the 1 in the original formula.

  =SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,A1:A10)) back to the top

