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)

🗒 Notes and Resources
Download a copy of the file used in this video: [ Ссылка ]

The formulas that I used in the demos:
=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)))

✅ Want more FREE training?
Why not check out my other tutorials and/or subscribe to my newsletter, both of which can be done at [ Ссылка ]

🙋‍♂️ Let's connect on social
Twitter: [ Ссылка ]
Facebook: [ Ссылка ]
LinkedIn: [ Ссылка ]

#excel #uniquecount #distinctcount

свернуть