XL: How to Count Unique Elements in a Cell Range

 Last reviewed: February 2, 1998
Article 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: 4.0 4.00 5.00 5.00a 5.00c 7.00 7.00a 97 98 XL98
XL97 XL7 XL5 XL4
Version : WINDOWS:4.0,5.0,7.0,97; MACINTOSH:4.0,5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.