How to Count unique values with CountIf function?

A common function many people use is the COUNTIF function and that will count any value you choose.

But there isn’t one function to count unique values. But we can make an array formula with 2 functions to do this. We start off with this : =SUM(1/COUNTIF(A1:A10,A1:A10))

But then instead of pressing Enter, you press CTRL + SHIFT + ENTER

What this does is to make the function an array. You need to do this step or else the formula won’t work.

You will get the following when you do that:

It shows 7 unique in that range which is correct.

This is a very cool way to get unique values without doing any pivot table or adding any helper columns to find this out.

Explore the comprehensive list of Excel Formulae here, to speed through your spreadsheet work.

Learn how to write VB Macros in Microsoft Excel with our specialized course on Excel Automation here.

Happy Excelling
Team Excelgoodies

Excel Formulas