Count Unique Items in an Excel Pivot Table

After you create an Excel pivot table, you might want to know how many unique customers placed an order for each product. However, when you add the Customer field to the pivot table’s Value area, it shows the number of orders, not the number of unique customers.

[Note: In Excel 2010 you can use PowerPivot to create a unique count]

CountUnique01

Unfortunately, a pivot table doesn’t have a built-in function to calculate a unique count. As a workaround, you could add a column to the source data, then add that field to the pivot table.

Add a Field to the Source Data

In this example, we want to count the number of unique Customer who ordered each product. We’ll add a column to the pivot table source data, with the heading ‘CustProd’.

In the CustProd column , we’ll enter a formula that refers to the customer (B) and product (E) columns.

=IF(SUMPRODUCT(($B$2:$B2=B2)*($E$2:$E2=E2))>1,0,1)

CountUnique02

With this formula, if the row contains the first instance of a customer/product combination, the result is 1. For subsequent instances, the result is 0.

Add the Field to the Pivot Table

After you create the new field in the source data, copy the formula down to the last row of data.

Then add the CustProd field to the pivot table Values area using the Sum function. In the screenshot below, you can see the Sum of CustProd field.

CountUnique03

Based on the new CustProd field, we can see that 11 unique customers placed orders for a Binder, and only 7 unique customers ordered a Pen Set.

_______________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in Custom Calculations, Excel Pivot Tables. Bookmark the permalink.

29 Responses to Count Unique Items in an Excel Pivot Table

  1. Guy says:

    Hi. many thanks for this tip it helped solve a tricky problem. Now I’m getting greedy! How would I show two dimensions of ‘uniqueness’? If I have 3 products and multiple customers, in multiple sales regions how would I show how many unique customers in london bought pencils and binders. I tried adding another column to the source data that used customer and product as the SumProduct arrays and I suspect the resulting pivot table is ok but the data is presented in a really confusing way that would make no sense to the casual reader; e.g. it shows 65 customers in london (Good), but the total transactions add up to greater than 65 as some customers bought both products. Hope this makes sense and would appreciate any help! Many thanks.

  2. Kalin says:

    Thank a lot. Very useful hint !

  3. Emma says:

    Hi,

    I found your count unique values in a pivot table formula…which was exactly what I was looking to do, however there are lines showing up for a given part number the the ‘sum of the customer count’ shows a value of 0…when I blow this out into it’s own worksheet it does in fact show only 1 line of data against that part number. Why will it not show a qty of 1 which is really what it should be based on how the data is displayed. Can you help?

  4. @Emma, if you see items with 0 sum, you might have applied a filter, so the first item (the one with the 1 result) isn’t visible.

    This formula is just a workaround, so it doesn’t adapt to changes, the way the other summarized amounts do.

  5. Oliver says:

    Hello,

    The formual works great, but i tend to want to understand what i use. I do get what the formula does but do not understand how the syntax works.

    -what does the following statement cause insside the funtion$Q$2:$Q2=Q2
    -why do i need to use th * rather than the , between ($Q$2:$Q2=Q2)*($S$2:$S2=S2)

    Thanks

    Oliver

  6. @Oliver, the formula looks in the cells starting in row 2, down to the active row. It checks for values that match the value in the current row.

    This SUMPRODUCT formula uses only two columns, so the * operator multiplies the TRUE/FALSE results in those columns.

    If you’re using SUMPRODUCT with more than 2 columns, you can separate with commas, as shown in the example here:

    http://www.contextures.com/xlFunctions01.html#SumProduct

    Also, Bob Phillips has good information and examples for SUMPRODUCT:

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

  7. D says:

    Thanks!! It worked for me..

  8. D2 says:

    What if you just want to look for unique values in ONE column?

  9. D3 says:

    What if I want to find unique values within a time range?
    Lets say that I have in column A multiple dates and in column B multiple records that could be repeated among the dates but I want to find how many unique values were during each day, how would do that ?

    Thanks in advance

  10. Marilyn Fleming says:

    Debra Dalgleish …thanks for replying to Oliver. It really helped me as well.
    Marilyn

  11. Guiomar says:

    Thank you! it is an easy way to work around the problem of counting unique values :O)

  12. gerry says:

    thanks. best solution i’ve read.

  13. Fred says:

    Yess! Exactly the solution I was looking for! I tried so many other places, but this is the one! Good job and a Big Thanks!

  14. D3 says:

    In response to my own question (if anyone cares), just download Powerpivot….it will make your life easier (distinct count and DAX language)

    best

  15. Andrew says:

    Hmmmmm Not sure about this but have tried this and the formula gives me a false rather than a value….

  16. Ryan says:

    I can’t believe it’s so hard to count unique entries. This workaround does not allow for filtering. I have 10,000 records spanning a year’s worth of data. If I want to see how many unique clients I had in January, this formula doesn’t work. It will only show unique clients for the entire data set. If all of my clients have been with me since the beginning of the data set, then I will show zero unique clients for January of this year, which is incorrect. If I modify the formula to start the count over each month then I am inflating my overall unique count for the year by an unknown factor.

    It just amazes me that something so easy for me to do (though tedious) is near impossible to do in Excel. It also amazes me that over the 20 year history of Microsoft Excel, Microsoft has not deemed the “unique entries” problem to be worth addressing… It further amazes me that if I add my clients’ names to the rows, IT SHOWS ME THE UNIQUE ENTRIES!!! So it’s already doing the hard part (finding the unique entries), the problem is that it just won’t count them!!! This totally baffles me!!!

  17. D3 says:

    Ryan,

    Just download Powerpivot! that’s all you need to get better pivot tables and actually use the distinct counts and much more, don’t forget that Powerpivot its a Microsoft product developed by the SQL Server Team.

    Hope this helps,

  18. Sussay says:

    It’s quite absurd that Excel pivot tables don’t have a “Count Distinct” function. This is a pretty ubiquitous need in analysis. And this “workaround” is pretty lame, because if you want a distinct occurrance with in a certain time frame, this doesn’t address. Microsoft Excel 2010 = LAME!

  19. lazyvba says:

    Hello,

    Waiting also for a built in solution from microsoft, I did implement a distinct count function in pivot with some pivot.

    Not the most elegant solution, especially on huge spreadsheets, but does the job by providing a reliable unique count within the pivot ( working with multiple dimensions)

    Regards

  20. kaeldowdy says:

    Thanks a lot…exactly what I was looking for! Wish Excel had this built into the option for COUNT DISTINCT or something like that! Perhaps with Excel 2013 they now have it…

  21. Brenda says:

    Will PowerPivot work with a home user – no server?

  22. D3 says:

    Brenda,

    All you need to use powerpivot is Excel 2010 or later. No server needed.

    Let me know if you have any other questions!
    Miguel

  23. Biff says:

    Oddly, I’ve copied this example exactly, but the formula won’t work. It’s giving me all 1s, right down.

  24. Biff says:

    Nevermind, the person asking me to fix their formulas had calculate set to manual. Which gave me a few hours of additional work…

  25. Arun Kumar says:

    Thanks Debra. Your post really helps.

  26. Pingback: Calculating the Sum of Averages in a Pivot Table

  27. Chandra says:

    This is very useful ! Thanks a ton !!

  28. Rhett says:

    This worked for me as well. I used it to get a count of unique vendors that had submitted invoices for a specific month. I used the formula combination on Vendor # and Submission Month, and when I made it a General data type format, I got the 0s and 1s described above. Nice Tip!

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>