A unique count (also called a distinct count) represents the number of unique values in a range. In the screenshot below the data in columns A:D relates to a fictitious company’s European stores.
They have 5 stores in France: 2 in Paris, 2 in Bordeaux and 1 in Marseille. A standard count of the City column (“How many cities are there where country is France?”) results in 5. A unique count results in 3 as there are 3 different values…Paris, Bordeaux and Marseille.
There are several ways to generate a count of unique values in Excel. One of the most common ways is to use a Pivot Table. However that requires the source data to be stored in the Data Model. That in itself presents challenges. Many Excel users have never heard of the Data Model and if you’re a Mac user or you use Excel for the Web, those two platforms don’t even support the Data Model.
In this video I count unique values by using a formula and I end up with something that looks like a Pivot Table but isn’t a Pivot Table.
As an added bonus, I show you how to create a Filled Map Chart from the numbers generated from the formula. Whilst you can create some types of chart from Pivot Tables, a Filled Map Chart isn’t one of them.
One caveat to this solution is that it uses several functions that are only available in Excel 365 and Excel 2021 (UNIQUE, FILTER and SORT).
The formulas that I used in the solution are:
=COUNTIFS($C$2:$C$13,F2)
=COUNTA(UNIQUE(FILTER($B$2:$B$13, $C$2:$C$13=F2)))
=SORT(UNIQUE(C2:C13))
=FILTER(B2:B13, C2:C13=F2)
=UNIQUE(FILTER(B2:B13, C2:C13=F2))
=COUNTA(UNIQUE(FILTER(B2:B13, C2:C13=F2)))
Download a copy of the file I used in this video: https://share.zight.com/6qu1rAXR