Editor’s Note:This is the last post created by Frank Kabel before his untimely death. I found it in the unpublished drafts bin and can’t think of any reason not to post it. So here it is:

One question which frequently appears in the Excel NGs is how to count unique entries. The following is an excerpt from a White Paper Bob Phillips and I created together.

As typical for Excel there’s not only one single formula to achieve this goal but several. Some of the most common ones are presented below:

1. Formula: =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))
2. Formula: =SUMPRODUCT((A1:A10<>””)/(COUNTIF(A1:A10,A1:A10&””)))
3. Formula: =SUMPRODUCT((A1:A10<>””)/(COUNTIF(A1:A10,A1:A10)+(A1:A10=””)))

Notes:

• Formula (1) is an array formulas entered with CTRL+SHIFT+ENTER.
• All formulas ignore blank cells and zero length strings (=””)
• All formulas can deal with mixed type of data (e.g. numbers, strings, etc.)
• Formula (2) is only completely robust starting with Excel 2003. In previous versions you have to be sure what the data range (in our example A1:A10) is completely within the used range of your spreadsheet. Otherwise this formula returns #DIV/0. You can check this in a new/fresh workbook by simply entering this formula. To prevent this bug use formula (3) instead

Looking at the performance of these formulas formula (1) is much faster than the other two formulas (nearly 4-5 times faster). So my recommendation would be to use formula (1) if performance is an issue or formula (3) if performance is not that important for you and you don’t want to use an array formula.

If you want to learn more about these formulas (how they work, detailed benchmarks, etc.) you may have a look at the above mentioned White Paper.