From the course: Excel 2016: Advanced Formulas and Functions

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Counting unique entries in a range with an array formula

Counting unique entries in a range with an array formula

From the course: Excel 2016: Advanced Formulas and Functions

Start my 1-month free trial

Counting unique entries in a range with an array formula

“

- I wish there were a function called Unique, where you could simply type in =Unique, refer to a range, and it would tell you how many entries there are, how many different unique entries. Now, we can do this by way of an array formula, but it's somewhat complex and a little convoluted looking. I discovered this particular formula about 15-20 years ago, used it ever since. For a while I didn't quite understand how it works. I'll explain that in a bit with a much smaller list. Let's start with this idea, though, that we've got a list over here. We cannot use entire column references in our formulas. I'm going to click on one of the cells in Column B, double click the bottom edge just to remind myself it's 687 rows that we're looking at here. Here's the formula, the array formula, that will count the number of unique entries in that range. =SUM(1÷COUNTIF( I can highlight all the cells or drag across some of them, B2 down to B6, and I'll simply type in the 87, and we need to use that…

Contents