• 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 PivotTables Unique Count 3 Ways

You are here: Home / Excel PivotTables / Excel PivotTables Unique Count 3 Ways
Excel PivotTables Unique Count 3 Ways
February 27, 2013 by Mynda Treacy

PivotTables are the Origamists of Excel. Folding and summarising data into almost any shape. I say 'almost' because until Excel 2013 they couldn’t calculate a unique (sometimes called distinct) count.

For example, you might want to count the number of 'things' you had, whether it be products or customers, or any other unique combination of records you might want to count.

In the example below we can see the count for each product, but this is actually the count of the number of records for each product, as opposed to the number of unique products.

Excel PivotTable Distinct Count

Bummer. You'd think something so simple would be available. Not to worry. Let's take a look at 3 different ways we can count unique items in a PivotTable.

The idea being that you'll find a solution that works with the version of Excel you use.

1. Excel PivotTable Count Unique Items pre 2013

Pre Excel 2013 you need to use a workaround, which is this:

  1. Add a helper column to your raw data with a formula that counts a 1 for the first instance of the product, and a zero for any duplicates.

Excel PivotTable Distinct Count

In cell E2 I entered this formula:

=IF(COUNTIF($B$2:B2,B2)=1,1,0)

Then copied it down the column.

Now I can include a sum of my new ‘Distinct’ column in my PivotTable like this:

Excel PivotTable Distinct Count

And below I further summarised the Products into their Categories which shows that I have 77 unique products, with the highest number being in the Confections category:

Excel PivotTable Distinct Count

Remember the 'Count of Product' is actually the count of records (in this case they're sales) for each product.

If you had multiple fields you wanted to combine to count unique combinations you could use the COUNTIFS function. As in the example below where I want to count unique combinations of the Region and Business Unit.

Excel PivotTable Distinct Count

Or if you have Excel 2003 you could use a SUMPRODUCT function like this:

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

You could then sum your unique Region/Business Unit combinations like this:

Excel PivotTable Distinct Count

2. Excel 2010 PowerPivot Distinct Count

If you’ve got the free Excel 2010 PowerPivot addin installed you can go ahead and try this method.

  1. Insert a PivotTable from within PowerPivot.
  2. Right click on the table name in the PowerPivot Field list and select ‘Add New Measure…’.

    Excel PivotTable Distinct Count

  3. Give your Measure a name (this is the field name that will appear in your PowerPivot field list).
  4. Enter your formula =COUNTROWS(DISTINCT('Product Raw Data'[Product]))

Excel PivotTable Distinct Count

Voila, you have a unique count of your products:

Excel PivotTable Distinct Count

3. Excel 2013 PivotTable Distinct Count

Click here for step by step instructions on the Distinct Count in Excel PivotTables in Excel 2013 onwards.

What Do You Think?

Did you like this? Let me know by sharing it using the social media icons below, or leave a comment.

Excel PivotTables Unique Count 3 Ways

More Excel Pivot Tables Posts

More Pivot Table Posts

Excel Pivot Tables Year on Year Change

How to calculate Year on Year variances in Pivot Tables
Sorting in Excel PivotTables

Sorting in Excel PivotTables

Excel Factor 6 Auto Refresh PivotTables

Excel Factor 6 Auto Refresh PivotTables

Excel Factor Entry 1 - Reverse PivotTable

Excel Factor Entry 1 – Reverse PivotTable

Create a Pivot Table Direct From Access

Create a Pivot Table Direct From Access

More Pivot Tables Posts

fixing incorrect totals in dax

Fixing Incorrect Totals in DAX

If you're getting incorrect totals in your DAX measures, this post explains how to fix them. VIDEO and Sample file to download.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
excel pivot table group data

Excel PivotTable Group Data [Video]

How to group data like dates into years, quarters or months, and ages into groups. Video instructions and sample workbook to download.

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you how to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.
Category: Excel PivotTablesTag: excel pivot tables, pivot table, pivot tables
Previous Post:Excel Surveys an Easy Way to Collect DataExcel Surveys (Forms) an Easy Way to Collect Data
Next Post:Excel Conditional Formatting with FormulasExcel Conditional Formatting with Formulas

Reader Interactions

Comments

  1. Cheryl Davis

    August 24, 2019 at 7:48 am

    I’m trying to set up a pivot table with a distinct count, and then the percent of column total. I use distinct count a LOT! The problem is the percents don’t add up to 100% (even though that is what you see at the bottom of the pivot table. Is there a way to get percents of distinct counts? Why doesn’t it work?

    Thanks!
    Cheryl

    Reply
    • Mynda Treacy

      August 24, 2019 at 7:13 pm

      Hi Cheryl,

      You’ll most likely need Power Pivot for this type of calculation. Please post your question and sample Excel file on our Excel Forum where we can help you further.

      Mynda

      Reply
  2. Theo

    February 5, 2018 at 10:31 pm

    Thank you, it worked a treat.

    Reply
    • Mynda Treacy

      February 6, 2018 at 8:08 am

      Great. Glad I could help.

      Reply
  3. Sam

    January 19, 2018 at 11:06 pm

    It was insightful

    Reply
  4. Gibby

    June 26, 2017 at 10:15 pm

    Thank you so much for your Excel-lent posts and Webinars.

    Reply
    • Philip Treacy

      June 26, 2017 at 10:54 pm

      You’re welcome 🙂

      Reply
  5. M Khurram Mumtaz

    December 6, 2016 at 6:33 pm

    Thanks a lot for sharing such useful information. Its Great

    Reply
    • Mynda Treacy

      December 6, 2016 at 7:57 pm

      You’re welcome 🙂 Glad we can help.

      Reply
  6. Dan Karo

    March 1, 2016 at 3:02 am

    your site is very rich . i have learnt so much.

    Reply
    • Mynda Treacy

      March 1, 2016 at 8:40 am

      Thanks! Glad you’ve found it useful, Dan 🙂

      Reply
  7. Wendy Farkas

    July 17, 2015 at 12:47 am

    Thanks so much for this info. Tip #1 was just what I needed to solve a pivot table challenge. I had created one to count # of clients seen each month, but also wanted a column to show # days worked. (My detail sheet had duplicate dates for seeing multiple clients in a day). Adding the helper column with your formula worked like a charm.

    You’re my hero, as always.

    Reply
    • Mynda Treacy

      July 17, 2015 at 9:11 am

      Aw, thanks Wendy 🙂

      Glad we could help.

      Mynda

      Reply
  8. Lorri Lenz

    October 31, 2014 at 1:39 am

    I am using the first solution from this article (=IF(COUNTIF($B$2:B2,B2)=1,1,0)) and it works like a charm. The problem is that my spreadsheet has the data refreshed as often as the user would like (they can do it daily or even several times a day) and I have VBA to add the formula and update the associated pivot tables to refresh the report. There are over 24,000 lines of data I am copying this formula to and it is taking a very long time for Excel to process…close to 10 minutes. Is there any way to speed this up?

    Reply
    • Catalin Bombea

      October 31, 2014 at 3:58 am

      Hi Lorri,
      Depends on what your vba code does…
      If you apply the formula with vba in a loop like:
      For i=2 to 24000
      Range("A" & i).Formula="=..."
      Next

      This will take a lot of time, even if screen updating is turned off.

      But if you apply the formula with vba to the entire range like: Range("A1:A24000").Formula="=...", this is much faster.
      If you need further help, a sample file will be needed, with your code. You can use our Help Desk if this doesn’t solve your problem.
      Cheers,
      Catalin

      Reply
  9. dave

    June 3, 2013 at 9:38 pm

    Hi guys. First of all thanks for your attention. Easy question

    I’m using this syntax in excel power pivot table

    =DISTINCT(ALL(Table2[sedi]) to get as result all the unique values that the field “sedi” displays, despite filters applied in the corresponding pivot table

    But i got as result “The DISTINCT function expects a column reference expression for argument ‘1’, but a table expression was used.”

    Any tips???

    thnx
    Dave

    Reply
    • Philip Treacy

      June 4, 2013 at 8:44 pm

      Hi Dave,

      The DISTINCT function returns a single column table that contains unique values from the specified column. But, DISTINCT can’t return values into a cell or column on a worksheet – you nest the DISTINCT function within another formula, to get a list of distinct values that can then be counted, summed, or used for other operations.

      So in this case you’d need to do something like this :

      =COUNTROWS(DISTINCT(ALL(Table2[sedi])))

      With regards to the specific error you received, sedi must be a column.

      Regards

      Phil

      Reply
  10. Damion Mcguire

    May 9, 2013 at 8:57 am

    PowerPivot for Excel can be installed on a computer that has 32-bit or 64-bit Excel 2010. If you have installed the 32-bit version of Excel, you must install the 32-bit version of PowerPivot for Excel. Likewise, if you have installed the 64-bit version of Excel, you must install the 64-bit version of PowerPivot for Excel.

    Reply
  11. Rob K

    May 3, 2013 at 2:49 pm

    Hi Mynda,

    Thanks for posting this, it helped me complete a key part of a project at work today! I was able to reference your “IF(COUNTIF($B$2:B2,B2)=1,1,0)” seen in Option #1 in order to get a Distinct Count column in the data set I was working with.

    For whatever its worth, larger data sets similar to mine may have trouble looping through the “$B$2:B2” portion of the statement above. The formula I created (edited to match your Beverages, Apple Juice, etc data set in Option 1 above) is “=IF(AND(A2=A1, B2=B1), 0, 1)”.

    NOTE: A key first step before using this “=IF(AND(A2=A1, B2=B1), 0, 1)” formula is to sort both Column A and Column B from A-Z (alphabetical order). This formula can then be placed in cell E2 and copied down the column. This formula compares the value in both column A and B for a particular row to the row directly above it, which is why sorting A-Z is so important.

    Feel free to pass this formula along to others in future training.

    Thanks again Mynda, wouldn’t have been to work through this as easily if it wasn’t for you sharing your knowledge here!

    Rob

    Reply
    • Mynda Treacy

      May 3, 2013 at 3:22 pm

      Good points, Rob. Thanks for sharing 🙂

      Reply
  12. F S

    April 30, 2013 at 3:34 am

    Hi, I am a little confused on how this works.

    I have a data set on students and I want a unique count of students in a year. I tried your method with the countif formula and then I also used the ‘remove duplicates’ sub tab under the data tab (excel 2010). Both gave me different results. Using the formula gave me a lower count as compared to the ‘remove duplicates’. Why?

    I checked the latter and found no duplicates and so my confusion.

    Reply
    • Mynda Treacy

      May 1, 2013 at 9:51 pm

      Hi FS,

      I’m not sure why you’d get different answers. Are you able to send me the file so I can try to understand what is going on.

      Thanks,

      Mynda.

      Reply
  13. Rachael

    March 1, 2013 at 6:52 am

    Fantastic. These tips are the perfect learning tool.
    Specific, clearly explained, and not too-much-at-once, they are a perfect way to increase my Excel skill level.
    Thankyou Mynda

    Reply
    • Mynda Treacy

      March 1, 2013 at 11:38 am

      Wow, thanks, Rachael 🙂

      Reply
  14. A S

    February 27, 2013 at 4:57 pm

    Valuable article indeed! The no. of times I have been bemused with the weird nos. in pivot is countless. Thanks for highlighting the problem and solution. I use excel 2010, so the powerpivot setting should help. Though my computer refuses to let me instal powerpivot add in.. Don’t know what the issue is… Any idea what the reason could be.

    Thanks,

    Reply
    • Carlo Estopia

      February 27, 2013 at 6:36 pm

      Hi AS,

      Try this one:

      If you do not see the PowerPivot tab after you install Office 2010 and PowerPivot for Excel, try the following:
      
          Load the add-in by clicking File, Options, and then Add-ins. In the Add-ins area, click Manage, select COM Add-ins, and click Go. In the COM Add-ins window, select the Microsoft.AnalysisService.Modeler.FieldList.Addin.Integration check box and click OK.
      
          If the add-in does not appear after you completed the above steps, and you are running Windows XP and do not have SP3 installed. You will need to install SP3 in order to use PowerPivot. You can download SP3 from the following location: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=68c48dad-bc34-40be-8d85-6bb4f56f5110
      
          If the add-in still does not appear and you installed Excel 2010 and PowerPivot only, you will need to install Office Shared Tools also. With the Office Beta, VSTO is installed when Office Shared Tools is installed. If this is the situation that you are encountering, you need to uninstall PowerPivot for Excel and Excel 2010. Next, install Excel 2010 and Office Shared Tools, and then install PowerPivot for Excel.
      

      source: Microsoft Support

      Cheers.

      CarloE

      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