XL: How to Count Unique Elements in a Cell Range

ID: Q90400

The information in this article applies to:

Microsoft Excel for Windows, versions 4.0, 5.0, 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition

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. Thus, 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),""),
      IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>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.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 178-179

Additional query words: 5.00a 5.00c 7.00a 97 98 XL98 XL97 XL7 XL5 XL4

Keywords :
Version : WINDOWS:4.0,5.0,7.0,97; MACINTOSH:4.0,5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type : kbhowto