Excel / Power Query – Distinct count

How many items do I have in my list? This is one of the questions that many people ask often. In this blog, you will learn 4 ways that help you to get the distinct number of items in a list.
The list is: a, b, c, d, a, b, e, d, z.

Method 1: Array formula

We can use this array formula {=SUM(1/COUNTIF(B4:B12,B4:B12))}. This formula will divide 1 by the count of each item and sum all the results. For example, if we have a list with 2 a and 1 b, for the item “a”, we will get ½ and ½ (because it appears 2 times) and for the item “b”, we will get 1/1, then the formula will sum ½, ½ and 1/1 to get a result of 2.

Method 2: Pivot table

We create a pivot table by adding the table to the data model then choose to summarize value by distinct count and we will get the distinct count.

Method 3: Power Query

After adding our table to Power Query, we delete duplicate values from the list then in Transform tab, we click on Count Rows and we get the result.

Method 4: Dynamic array formula

With the new UNIQUE() function (you can use this function only if you are in the Insider program of Office 365), we can get the list of items without duplicates then count the number in this new list.

Hope that these ways will help you. You can download the Excel workbook here. In this blog, I used the simplest ways, we can get the same result by using Power Pivot or a VBA macro.
If you have any question, don’t hesitate to contact me. See you 😊

Publicités

2 commentaires sur “Excel / Power Query – Distinct count

Répondre

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion /  Changer )

Photo Google

Vous commentez à l'aide de votre compte Google. Déconnexion /  Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s