Counting the number of values filtered by UNIQUE()

[1453_walkers_co_uk]

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.

[#2 · Accepted solution]

@1453_walkers_co_uk 

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)

[#3]

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

[#4]

@1453_walkers_co_uk 

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.