Counting Unique Values Across Multiple Columns

Hello, I am trying to count unique values across multiple columns. I am exporting student activities from my work database. You can see from my example how the database exports this information. ...

Activity Count Question Example.xlsx12 KB

excel

Riny_van_Eekelen

Platinum Contributor

Sep 17, 2021

OliviaBurglund As a variant, perhaps you want to consider PowerQuery. Connect to the table with activities, flatten it with "unpivot" and group (count) by activity. Just a few clicks. No complicated formulae needed. See attached.

Activity Count Question Example-2.xlsx20 KB

PeterBartholomew1

Silver Contributor

Sep 17, 2021

Riny_van_Eekelen

Unpivot other columns is tailor-made for the job! The grouping also works effectively.

I will have to think further on the questions of 'Does a complicated formula cease to be complicated merely because one can select it at the click of a button?' I can see arguments both ways!

I actually have Charles Williams's FastExcel add-in, so I could have gone for

= LET(
  fullList, INDEX(UNPIVOT(data,,1,,,0),,3),
  COUNTIFS(ActivityGrid, SORT(UNIQUE(fullList))))

Despite that, I think I have yet to find the definitive dynamic array solution for unpivotting that does not rely upon add-ins. Playing with indices works but is somewhat inelegant.

Forum Discussion

Counting Unique Values Across Multiple Columns

Resources