Counting Non Duplicate Entries in a Range in Excel

When we are doing analysis in excel or preparing the reports, we will face situations where we need to calculate the unique entries. There is no direct function in Excel to calculate only non duplicate entries in a Range. However this can be achieved through array functions of Excel. Below is the example through which we will learn to use Excel function to calculate non duplicate entries count.

The above example has list of gadgets in Column A2 to A10. However there are duplicates in List. Our goal is calculate unique number of Gadgets. So the formula to count non duplicate entry is
=SUM(1/COUNTIF(A2:A10,A2:A10)) which returns 5. i.e out of 9 entries only 5 items are unique.
Note: For using array functions, press Ctrl+Shift+Enter(Not Just Enter). Excel surrounds the formula with braces to remind you that it is a array formula.
This formula is first used by David Hager and then passed around on various web sites and newsgroups. The  above array formula works fine unless the range contains one or empty cells. To overcome this short fall we should use below formula with new IFERROR function.
=SUM(IFERROR(1/COUNTIF(A1:A10,A1:A10),0))
The preceding formula work with both values and text. If the range contains only numeric values or blank cells(But no text), we can use the below mentioned formula(This is not array formula) to count the number of non duplicate or unique values.
=SUM(N(FREQUENCY(A1:A10,A1:A10)>0))