Forum Discussion
Solved
Counting the number of values filtered by UNIQUE()
Hello, I have a table which I have filtered down by region (to only show Yorkshire depot box sales) which looks like so: Which I have then filtered to remove the duplicate values with UNIQUE(A...
After extracting unique values by function UNIQUE() use COUNTIFS() to count those unique values.
=COUNTIFS(A2:A14,F2:F4,B2:B14,G2:G4,C2:C14,H2:H4,D2:D14,I2:I4)
After extracting unique values by function UNIQUE() use COUNTIFS() to count those unique values.
=COUNTIFS(A2:A14,F2:F4,B2:B14,G2:G4,C2:C14,H2:H4,D2:D14,I2:I4)
Wow! You're incredible! I didn't know that syntax was possible! Thank you!
You are welcome. For my own interest I have build this formula to extract uniques and then count just by one formula.
=LET(x,A2:D14,y,BYROW(x,LAMBDA(a,CONCAT(a))),b,UNIQUE(y),c,BYROW(b,LAMBDA(t,COUNTA(FILTER(y,y=t)))),CHOOSE({1,2,3,4,5},INDEX(UNIQUE(x),,1),INDEX(UNIQUE(x),,2),INDEX(UNIQUE(x),,3),INDEX(UNIQUE(x),,4),c))Download the attach file.