Excelgoodies
+44 (0)20 3769 3689
Excelgoodies logo +44 (0)20 3769 3689
+44 (0)20 3769 3689  

 Business Professionals

 Techno-Business Professionals

 Technology Professionals

Home

|

About Us

|

Contact Us

Programs & Courses

New

Home | About Us | Contact Us

+44 (0)20 3769 3689

support@excelgoodies.uk

ExcelGoodies WorldwideUK | USA | Australia | Singapore | Phillipinnes

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

New

Next Batches Now Live

Power BIPower BI
Power BISQL
Power BIPower Apps
Power BIPower Automate
Power BIMicrosoft Fabrics
Power BIAzure Data Engineering
Explore Dates & Reserve Your Spot → Reserve Your Spot →

Related Articles