SOLVED

New Contributor

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.

3 Replies
best response confirmed by 1453_walkers_co_uk (New Contributor)
Solution

Re: Counting the number of values filtered by UNIQUE()

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)``

Re: Counting the number of values filtered by UNIQUE()

Wow! You're incredible! I didn't know that syntax was possible! Thank you!

Re: Counting the number of values filtered by UNIQUE()

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))``