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”

1. Mehdi HAMMADI dit :

Sympa l’article, es-tu inscrit au programme Office Insider car ce n’est pas mon cas et je suis impatient de travailler avec les nouvelles fonctions matricielles

Aimé par 1 personne

1. Hoss SATOUR dit :

oui, beaucoup de nouvelles fonctionnalités très intéressantes

J'aime