Microsoft Office Tutorials and References

In Depth Information

**Tip 44: Counting Nonduplicated Entries in a Range**

The preceding array formula works fine unless the range contains one or more empty cells. The

following modified version of this array formula uses the IFERROR function to overcome this problem:

=SUM(IFERROR(1/COUNTIF(A1:A10,A1:A10),0))

The preceding formulas work with both values and text. If the range contains only numeric values or

blank cells (but no text), you can use the following formula (which isnâ€™t an array formula) to count the

number of nonduplicated values:

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