Counting Unique Values Across Multiple Columns

Silver Contributor

Sep 17, 2021

For what it is worth, I have got a 365 solution working with beta release functions, so it is probably a little early to be useful.

The main formula is

= LET(
  fullList, REDUCE("",ActivityGrid,
    LAMBDA(acc,val,APPENDλ(val,acc))),
  SORT(UNIQUE(FILTER(fullList, LEN(fullList)>1))))

which runs across each row adding the value to a list. Then it filters out the blanks, removes duplicates and sorts the list to alphabetic order. It does however call a second Lambda function APPENDλ which I defined to be

= LAMBDA(value,list,
    LET(
      n, COUNTA(list),
      k, SEQUENCE(n+1),
      IF(k<=n,list,value)
    )
  )

Just for the hell of it, I turned the first formula into a named Lambda function so the two formulas for the result were

= UNPIVOTλ(ActivityGrid)

= COUNTIFS(ActivityGrid, UNPIVOTλ(ActivityGrid))

This is all to be standard Excel, just not quite as we have known it over the years!

Forum Discussion

Counting Unique Values Across Multiple Columns

Resources