Here I’m trying to count the unique number of times a user upvoted a case:
=ARRAYFORMULA(IF(B2:B <> "", COUNTIF(CHOOSECOLS(UNIQUE(FILTER({B2:B, D2:D}, D2:D <> "")), 2), J2:J) + 1,))
Works fine, but then I try to output the users who upvoted each case:
=ARRAYFORMULA(JOIN(", ", CHOOSECOLS(UNIQUE(FILTER({B2:B, D2:D}, MATCH(D2:D, J2:J, 0))), 1)))
This one isn’t working. What am I doing wrong? Here‘s a sample sheet.
>Solution :
You can’t use ARRAYFORMULA here because JOIN is an aggregate function and FILTER by default returns arrays. Use MAP instead:
=MAP(J2:J,LAMBDA(case,IF(case="",,JOIN(", ",UNIQUE(IFNA(FILTER(B2:B,D2:D=case)))))))