Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Count Only Unique numbers in a pivot table

Latest post Thu, Mar 13 2014 6:55 AM by Roger Govier. 20 replies.
  • Mon, Mar 24 2008 4:21 PM

    • rmartin
    • Not Ranked
    • Joined on Mon, Mar 24 2008
    • Posts 1
    • Points 37

    Count Only Unique numbers in a pivot table

    Within the data field of a pivot table, I know we can sum, count, average, etc,...but is there a way to get a count of unique entries/numbers there?

    Any help is much appreciated.

    Thanks!

    • Post Points: 37
  • Mon, Mar 24 2008 5:53 PM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Tue, Jan 8 2008
    • Abergavenny, South Wales, UK
    • Posts 281
    • Points 4,465
    • MVP

    Re: Count Only Unique numbers in a pivot table

    No you can't.

    You will need to create an extra column in the source table to display uniques, and extend the data source table to include the new column.
    In the new column use a formula like
    =IF(COUNTIF(A:A,A1)=1,1,0)

    Regards
    Roger Govier
    Microsoft Excel MVP

    • Post Points: 37
  • Wed, Apr 21 2010 4:36 AM In reply to

    • mark1uk
    • Not Ranked
    • Joined on Wed, Apr 21 2010
    • Posts 1
    • Points 37

    Re: Count Only Unique numbers in a pivot table

    That will count only where unique and totally ignore all duplicated cells - most people want a distinct count so that duplicates are counted once.

    so if you have 1000 rows use ...    =IF(COUNTIF(A1:A$1000,A1)=1,1,0)   

    • Post Points: 37
  • Tue, May 18 2010 6:53 PM In reply to

    • dmitryjoy
    • Not Ranked
    • Joined on Tue, May 18 2010
    • Posts 2
    • Points 26

    Re: Count Only Unique numbers in a pivot table

    I am really not understanding how this solution and others that are keep finding by googling are supposed to work.  All this formula does is (very slowly) indicate the first occurrence of a repeating item.  But in a pivot table, the intersection of pivots could result in a set of rows NONE of which include the first occurrence. Summing up the "first_unique" column in that case will simply result in a zero.  The issue here is that "pivot after unique"   -- which is what you are suggesting -- is not the same as "pivot after unique".  Am I missing something here?

    • Post Points: 5
  • Tue, May 18 2010 6:55 PM In reply to

    • dmitryjoy
    • Not Ranked
    • Joined on Tue, May 18 2010
    • Posts 2
    • Points 26

    Re: Count Only Unique numbers in a pivot table

    the last statement should read "pivot after unique" is not the same as "unique after pivot"

    • Post Points: 21
  • Wed, May 19 2010 11:05 AM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Tue, Jan 8 2008
    • Abergavenny, South Wales, UK
    • Posts 281
    • Points 4,465
    • MVP

    Re: Count Only Unique numbers in a pivot table

    Hi

    There was a mistake in my original formula as posted.

    =IF(COUNTIF(A:A,A1)=1,1,0)

    should have been

    =IF(COUNTIF($A$1:A1,A1)=1,1,0)

    If you are still having difficulties, mail me a copy of uor workbook.

    roger at technology4u dot co dot uk

    Change the at and dots to make valid email address.

    Regards
    Roger Govier
    Microsoft Excel MVP

    • Post Points: 53
  • Thu, Aug 26 2010 12:58 PM In reply to

    • yuchai
    • Not Ranked
    • Joined on Thu, Aug 26 2010
    • Posts 1
    • Points 21

    Re: Count Only Unique numbers in a pivot table

    I signed up for this forum just to say this: why in the world is this simple feature still not built in to Excel? How many versions since Excel's existence, and we still can't count unique items in a pivot table?

    • Post Points: 21
  • Fri, Nov 19 2010 5:22 AM In reply to

    • Lazyvba
    • Not Ranked
    • Joined on Fri, Nov 19 2010
    • Posts 2
    • Points 42

    Re: Count Only Unique numbers in a pivot table

    Hello,

    Waiting for an excel version implementing count, I wrote a code improving your pivot so it does provide unique count on the field you choose.

    It's just code to paste one time.

    let me know if it works for you.

     

    http://lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count.html

    • Post Points: 37
  • Fri, Nov 19 2010 5:54 AM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Tue, Jan 8 2008
    • Abergavenny, South Wales, UK
    • Posts 281
    • Points 4,465
    • MVP

    Re: Count Only Unique numbers in a pivot table

    Hi

    It would be nice if you could attach your file here (or on your blog) for downloading, as it would then be easier to see what unique items you were counting

    Regards
    Roger Govier
    Microsoft Excel MVP

    • Post Points: 37
  • Fri, Nov 19 2010 8:48 AM In reply to

    • Lazyvba
    • Not Ranked
    • Joined on Fri, Nov 19 2010
    • Posts 2
    • Points 42

    Re: Count Only Unique numbers in a pivot table

    Hello Roger,

    I will try to upload a file.

    I was always wondering where I could find dummy datas to create example, if you know.

    The code it self let you choose what field you want to count unioque items.

    Regards

    update: http://share1t.com/3zu6fl

    unique number of songs by artist, year, month.

    in fact a bad exemple as song only appears once in the source data.

    I will try to find something better

     

    • Post Points: 5
  • Mon, Jan 31 2011 8:42 PM In reply to

    Re: Count Only Unique numbers in a pivot table

    hi,

    I tried your version and it didn't work. I attached a file for your record.

    I want to show a count of the company names that Dan Brown handle (10 companies). But since there's duplicates in other months, it sums the name up. I want to remove the duplicate and count it only once. Is there a way to do that in the pivot table?

    • Post Points: 21
  • Tue, Feb 1 2011 3:09 AM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Tue, Jan 8 2008
    • Abergavenny, South Wales, UK
    • Posts 281
    • Points 4,465
    • MVP

    Re: Count Only Unique numbers in a pivot table

    Hi

    You need to change the attribute of Homogenized C. Conut to SUM not COUNT.

    Then you will see
    Brown  10
    Krista   22
    Smith  38

    Alternatively, if you want a coded solution, then look at the posting by Lazyvba where he has posted some excellent code top do this 

     

    Regards
    Roger Govier
    Microsoft Excel MVP

    • Post Points: 5
  • Tue, Dec 11 2012 3:19 PM In reply to

    • ccouch
    • Not Ranked
    • Joined on Tue, Dec 11 2012
    • Posts 1
    • Points 5

    Re: Count Only Unique numbers in a pivot table

    I joined just to say: Great solution!  it turns out that Lazyvba isn't as lazy a me.

    Thank you

    • Post Points: 5
  • Wed, Apr 3 2013 7:09 PM In reply to

    • Nathalie
    • Not Ranked
    • Joined on Wed, Apr 3 2013
    • Posts 1
    • Points 5

    Re: Count Only Unique numbers in a pivot table

    Thanks mark1uk!!! This SAVED my life. Much appreciated!!!

    • Post Points: 5
  • Mon, May 20 2013 4:10 AM In reply to

    • Tim
    • Not Ranked
    • Joined on Mon, May 20 2013
    • Posts 1
    • Points 5

    Re: Count Only Unique numbers in a pivot table

    I am an okay user of excel but, although this works - BIG THANK YOU - have no idea why or how?  I joined to say thanks.

    Thanks again,

    Tim

    • Post Points: 5
Page 1 of 2 (21 items) 1 2 Next > | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.