Another way to count unique values in a range in Excel

Not long ago I wrote an article about how to count the number of unique values in a range. That trick was basically using the SUMPRODUCT and COUNTIF functions. Today we are going to see another way to calculate it.

To understand what we are trying to do it is always best to use an example. We have a list of names in the range A1: A14, which we have called Name_list.

How many of these names are unique? The answer is obviously 11. But how we calculate it? We saw earlier that can be calculated using the SUMPRODUCT and COUNTIF functions, but today I want to show you another way to perform the same calculation, but this time using the FREQUENCY and MATCH functions.

And what formula is that?

An Excel dashboard about the UK general election 2015

Hello Excel-folks, today I want to share with you a nice informative Excel dashboard that I created for the UK general election on May 7th. I focused on the main 7 parties (those ones that appeared on the ITV debate). See how the dashboard looks like in a few screenshots:

The Excel dashboard about Real Madrid Tenth Champions League win 2013/14

Hello Excel fans. Today I would like to mix two of my passions. As you all know I love Excel, but I also love football and am a supporter of Real Madrid. Although it is best to have the full story so that everybody understands. My whole family are Atletico Madrid supporters, in fact I was born as an Atletico fan and I remember going to the Vicente Calderon when I was little, but when I was 5 years or so I changed my team and I became a fan of Real. Why? Because my favourite player was playing for Atletico and then he signed for Real, and I followed him.......   Who is he? The incredible Hugo Sanchez. I was so passionate about how he played and his somersaults as goal celebration, and besides, I also played football with the number 9. Anyway, so today I consider myself a fan of Real Madrid but I still have much appreciation to Atletico. So, back to Excel, I have decided to create a small tribute to Real Madrid for the achievement of the Champions League win last year: the Tenth European Cup, and everything will be done in Excel. So even if you are not Real Madrid supporter or do not even like football, check the dashboard and I am sure you will find it interesting.

First let's see how it looks:

How to count the number of unique values in a range in Excel

Hello, on many occasions we have a list or range with lots of data, and we wondered how many of those values or data are unique, and we have had to develop a complex solution by adding helper columns and doing multiple operations.

That is no longer necessary !!

Because we can calculate the number of unique values in a list with a simple formula.
And what formula is that ?? Let's see ...

Array formulas in Excel

What is an array ??

The term array refers to a collection of data residing in a column or row of Excel or a combination of them. In Excel, these elements may reside in a single row (what is called a one-dimensional horizontal array), a column (one-dimensional vertical array ) or multiple rows and columns (two-dimensional array). In Excel you can not create array formulas or arrays that are three-dimensional.

And an array formula ... ??

"I have enough with the formulas, and now you talk about array formulas.. Help!!"

...

If you feel like this right now wait, they are simpler than the name suggests, and learning them it is for Excel power users.