Counting the number of values filtered by UNIQUE()

New Contributor

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 of1453_walkers_co_uk_5-1659081255240.png

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)


After extracting unique values by function UNIQUE() use COUNTIFS() to count those unique values.





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.




Download the attach file.