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.
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:
- 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.
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:
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:
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.
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:
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.
- Insert a PivotTable from within PowerPivot.
- Right click on the table name in the PowerPivot Field list and select ‘Add New Measure…’.
- Give your Measure a name (this is the field name that will appear in your PowerPivot field list).
- Enter your formula =COUNTROWS(DISTINCT(‘Product Raw Data’[Product]))
Voila, you have a unique count of your products:
3. Excel 2013 PivotTable Distinct Count
Now, I’d like to show you step by step instructions on how to count unique items in Excel 2013 but after installing it a while ago I found that if I wanted to open an Excel file attachment from Outlook, (by double clicking on the file in the email) it would open it in Excel 2013. Makes sense since it’s the most up to date version, but I didn’t want to.
Call me old fashioned, but I wanted to keep opening them in 2010 (because most of the questions I get are about Excel 2007 or 2010), and despite considerable Googling I couldn’t find a way to change the default for Excel back to 2010.
The workaround was to save the file first and then open it from within Excel 2010 but this isn’t ideal. You see I get a lot of emails (a lot!) and I don’t want to have to save every file I get just so I can have a quick look at it.
So, I did the only thing I knew would work and I uninstalled Excel 2013.
I’ll get around to installing it again sometime soon but for now you can read this post by Michael Alexander on how to count distinct records in Excel 2013 using PivotTables.
What Do You Think?
Did you like this? Let me know by sharing it using the social media icons below, or leave a comment.
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?
Hi Lorri,
Depends on what your vba code does…
If you apply the formula with vba in a loop like:
For i=2 to 24000Range("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
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
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
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.
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
Good points, Rob. Thanks for sharing
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.
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.
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
Wow, thanks, Rachael
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,
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