Firstly, if all you want to do is filter out duplicates you can use the Advanced Filter tool to do so. Go to the Data tab, Sort & Filter, Advanced and select the ‘Unique records only’ tick box.
However, if you want to return the distinct count of a range then you need to use an array formula.
I used to use a long-winded method involving the FREQUENCY and MATCH functions until I discovered this more simple alternative.
Enter this formula (replacing “range” with the relevant cell references or named range).