Forum Discussion

OliviaBurglund's avatar
OliviaBurglund
Copper Contributor
Sep 16, 2021

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. ...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 16, 2021

OliviaBurglund 

A 365 solution will no pretence at being interested in legacy Excel!

= LET(
  padded, ActivityGrid&REPT(" ", 10-LEN(ActivityGrid)),
  concatenated, CONCAT(padded),
  k, SEQUENCE(20,1,1,10),
  fullList, TRIM(MID(concatenated,k,10)),
  list, SORT(UNIQUE(FILTER(fullList, LEN(fullList)>0))),
  IF({1,0}, list, COUNTIFS(ActivityGrid, list)))

Resources