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(A3:D20000)
I would like to get the number of values removed by UNIQUE(), not as in a grand total number of removed values, but rather how many duplicates of
where removed, in other words, I'm trying to calculate the number of boxes sold to Yorkshire on the 11th week of 2016 using functions. I don't wish to use anything other than functions.
Any ideas? Thank you.
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.
