Forum Discussion
Trying to count unique items from a data set with multiple conditions
Hi, I just joined this community and looking for some help! I have a set of data from which I am trying to count unique items under certain categories. For example, there are multiple categories ...
You can create a pivot table from the data.
In the 'PivotTable from table or range' dialog, tick the check box 'Add this data to the Data Model'.
Add Category to the Rows area and Product to the Values area.
Then click the drop-down in the Count of Product field in the Values area and change the summary function to Distinct Count.
Result:
- Hi Hans Vogelaar,
Thanks for the quick reply, really appreciate it. I will definitely try this, can there be a formula or function to extract the same information, just curious!
Anisul HaqueWith a formula and the data being tabled:
=LET( category, SORT(UNIQUE(Table1[Category])), UniqueProducts, LAMBDA(acc, v, LET( filtered, FILTER(Table1[Product], Table1[Category] = v), distinct, ROWS(UNIQUE(filtered)), VSTACK(acc, HSTACK(v, distinct)) ) ), REDUCE({"Category", "Count"}, category, UniqueProducts) )- Thank you, Patrick, really appreciate it.
