• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Factor 9 Count Unique Items in a List

You are here: Home / Excel Formulas / Excel Factor 9 Count Unique Items in a List
Excel Factor 9 Count Unique Items in a List
August 8, 2012 by Mynda Treacy
This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee.

There are times when you wish to determine how many unique entries are in a list.  Wouldn’t it be great to be able to select a range of cells, like the one shown below, and have a function return, in this case, the number 9?

Unfortunately, there is no single function in Excel that will determine this number.  We can, however, use a combination of MATCH and FREQUENCY functions nested with IF and SUM to produce the desired result.

FruitList would yield 9

Excel Count unique Items

Count number of unique items in a column of text

(with no blank cells)

The below formula will return the answer, provided the data has no blank cells interspersed with the data.

Non-Array Solution

=SUM(IF(FREQUENCY(MATCH(FruitList,FruitList,0),MATCH(FruitList, FruitList,0))>0,1))

Note: 'FruitList' is a named range for cells A1:A25.

If you’re feeling adventurous, you can accomplish the same result with an array formula.  Remember to use the CTRL and SHIFT key when you enter the formula so the system knows you are working with an array.

For information on arrays and their purpose, see the Array Functions in Excel tutorial.

Array Solution

(requires CTRL-SHIFT-ENTER)

=SUM(1/COUNTIF(FruitList,FruitList))

Count number of unique items in a column of text

(with blank cells)

If you find yourself in a situation where blank cells are mixed within your data, the following two examples will accommodate the blank cells.

Non-Array Solution

=SUMPRODUCT((FruitList<>"")*(1/COUNTIF(FruitList,FruitList &"")))

Array Solution

(requires CTRL-SHIFT-ENTER)

=SUM(IF(COUNTIF(FruitList,FruitList)=0,"",1/COUNTIF(FruitList,FruitList)))

When dealing with a PivotTable

If you need to know how many unique items there are in relation to another variable, e.g. Permits per Business Unit Operation, a PivotTable is a perfect tool for calculating those answers quickly.

Unfortunately (there’s that word again), the counting/summing tools built into the PivotTable are not able to distinguish between counting all items or one of each sub-category; it counts all instances of an entry.

In the example below, the PivotTable would return 8 for BRN-Dante and 15 for BRN-Green Valley.  We simply want to know how many permits were issued per BU-Operation.

In other words, 2 permits for BRN – Dante and 6 permits for BRN – Green Valley.

Excel Count unique Items

Create a new column in the data and add the following formula next to the first record (cell C2):

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

This will flag the first permit in each permit number group with 1 and all repeated permits with 0.

Excel Count unique Items

The column can then be added to a PivotTable with the SUM function as the operator, counting the number of unique permits per BU-Operation:

Excel Count unique Items

Thanks again, Bryon for sharing your knowledge.

Bryon is from Bristol, Tennessee and has been teaching Excel since version 7 which was included with Office 95. He is currently a Technical Training Analyst for one of the largest coal companies in the world.  His key responsibility is to conduct all Microsoft Office training, but in addition he also serves as a technical consultant for any and all projects involving Microsoft Office applications.

Bryon says “Excel is by far my favorite among all the Office applications due to the almost infinite number of situations it can be used.  There literally seems to be no end to its usefulness.

My favorite Excel tools are difficult to narrow down.  Without getting into third party add-ins, I would have to say PivotTables (especially with the addition of Slicers.  WOW!  Are those things awesome!!), the Text to Columns tool (what a time saver), and Macros.”

Vote for Bryon

If you’d like to vote for Bryon’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂

Excel Factor 9 Count Unique Items in a List

More COUNT Posts

More Excel_Factor Posts

Excel Factor Voting Roundup

Excel Factor Voting Roundup

Excel Factor 21 Hyperlink Triptych

Excel Factor 21 Hyperlink Triptych

Tips and tricks with hyperlinks to make them dynamically update as the selection in the sheet changes. Sample workbook available.
Excel Factor 20 Custom Number Format Disguise

Excel Factor 20 Custom Number Format Disguise

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

The Excel HYPERLINK function creates links to places inside or outside of your Excel file. I'll show you a shortcut for creating them that few people know.
Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 13 Handy Tips & Tricks

Excel Factor 13 Handy Tips & Tricks

More MATCH Posts

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: COUNT, Excel_Factor, MATCH
Previous Post:Excel Factor 8 Highlight Cells Containing FormulasExcel Factor 8 Highlight Cells Containing Formulas
Next Post:Excel Factor 10 Conditional Formatting Painting by NumbersExcel Factor 10 Conditional Formatting Painting by Numbers

Reader Interactions

Comments

  1. René Maagdenberg

    June 10, 2015 at 5:17 pm

    Looking for a solution to count the number of unique contracts other then advanced filter, I came across this array formula solution: =SUM(IF(FREQUENCY(IF(LEN(_Contracts)>0,MATCH(_Contracts,_Contracts,0),””),IF(LEN(_Contracts)>0,MATCH(_Contracts,_Contracts,0),””))>0,1)).
    This works, and is dealing with empty cells. But I am still trying to understand this formula (I have to take a closer look at FREQUENCY, which I haven’t dealt with before).
    Suddenly I remebered the great experience I have with myonlinetraininghub, so I decided to take a look at your site.
    Well, as expected, your tip is clear and easier to follow than the formula I found earlier.
    As for the pivot table option: in my opinion it is accurate to add only the field you want to count into the row section. a simple count will give you the desired result. You’ll have to refresh the pivot table when new data is added, as usual.

    Thanks again for making things clear and understandable!
    kind regards, René

    Reply
    • Mynda Treacy

      June 10, 2015 at 8:15 pm

      You’re welcome, René 🙂

      Reply
  2. Wigs

    October 8, 2014 at 8:24 pm

    Fantastic tip – many thanks
    I’d never have worked that out

    Reply
  3. Shannon

    February 27, 2014 at 2:38 am

    This post was incredibly helpful! I’ve been searching the web for days trying to find a formula solution to help with unique counts on text value columns. Your Sumproduct solution, along with a Sumifs formula worked perfectly!

    Reply
    • Philip Treacy

      February 27, 2014 at 9:22 am

      Hi Shannon,

      Glad we could help you.

      Regards

      Phil

      Reply
  4. VD

    August 9, 2012 at 2:52 am

    Great tip. Thanks!

    Reply
    • Mynda Treacy

      August 9, 2012 at 6:55 am

      Cheers, VD 🙂

      Reply
  5. Dnyandeo

    August 8, 2012 at 7:00 pm

    Really very good & useful in work.

    Reply
    • Mynda Treacy

      August 8, 2012 at 7:38 pm

      Cheers, Dnyandeo. I’m sure Bryon will be glad it’s helped you.

      Reply
  6. Good one Keep posting

    August 8, 2012 at 4:38 pm

    Its very interesting & Good for learning new things Thanks for all the team behind this. Keep updating. Thanks a lot –Tukaram 9395152764

    Reply
    • Mynda Treacy

      August 8, 2012 at 7:37 pm

      On behalf of Bryon, thanks, Tukaram 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x