Microsoft KB Archive/90400

= XL: How to Count Unique Elements in a Cell Range =

PSS ID Number: 90400

Article Last Modified on 5/23/2002

-

The information in this article applies to:


 * Microsoft Excel for Windows 4.0
 * Microsoft Excel for Windows 5.0
 * Microsoft Excel for Windows 5.0c
 * Microsoft Excel for Windows 95 7.0
 * Microsoft Excel for Windows 95 7.0a
 * Microsoft Excel for the Macintosh 4.0
 * Microsoft Excel for the Macintosh 5.0
 * Microsoft Excel for the Macintosh 5.0a

-



This article was previously published under Q90400



SUMMARY
In Microsoft Excel, you can use the FREQUENCY function in an array formula to count the unique elements in a cell range.



MORE INFORMATION
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 formula above, and those that follow, are array formulas and must be entered by pressing COMMAND+ENTER (if you are using Microsoft Excel for Windows, 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.

Because the FREQUENCY function works only with numbers, you must add a level for evaluating 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 above formula counts the number of unique text and numeric entries for A1:A10, where A1:A10 contains no blanks cells.

Adding an additional test allows unique numeric and text elements to be counted in a range that includes blank cells:

  =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))

By substituting the final a1:a10 for the 1 in the original formula it will now sum unique values for a range of cells.

  =SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,A1:A10))

NOTE: These formulas must be entered as array formulas in order to work correctly.

For more information regarding SUM(IF) formulas, use the appropriate reference below:


 * For Microsoft Excel for the Macintosh running System 6.x, click Help on the Window menu.
 * For Microsoft Excel for the Macintosh running System 7.0 or System 7.1, click Microsoft Excel Help on the Balloon help menu.
 * For Microsoft Excel 5.x for Windows, click Contents on the Help menu, select the Product Support option, and select the Answers to Common Questions option. The answers to questions 9-11 all use SUM(IF) formulas.

