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).
Then turn this into an array formula by selecting the cell, clicking into the formula bar, and then pressing Ctrl+Shift+Enter.