+ Reply to Thread
Results 1 to 4 of 4

Pivot Count Unique values - Excel 2010

Hybrid View

  1. 01-26-2017, 01:23 PM #1
    ats1312
    ats1312 is offline
    Registered User
    Join Date
    05-02-2013
    Location
    Dublin
    MS-Off Ver
    Excel 2016
    Posts
    62

    Pivot Count Unique values - Excel 2010

    Hi,
    I am trying to analyse a data set by summing the values per quarter but also the amount of invoices making up that sum. The Count Option on the pivot simply counts all entries which I do not want.

    I got a solution for the issue (=1/COUNTIF($A$2:$A$31,A2) then PIVOT the SUM, but it wont work on my original data as there are over 20,000 lines. The Pivot returns a sum of #DIV/!

    The attached has sample data including the solution that works well on this size data.

    Hope someone can help....thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-26-2017, 02:10 PM #2
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,666

    Re: Pivot Count Unique values - Excel 2010

    Try pasting the following formula into column D of the original data and see if that solves the issue:
    Formula: copy to clipboard 
    =IF(COUNTIF($A$2:A2,A2)=1,1,"")
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  3. 01-27-2017, 04:43 AM #3
    ats1312
    ats1312 is offline
    Registered User
    Join Date
    05-02-2013
    Location
    Dublin
    MS-Off Ver
    Excel 2016
    Posts
    62

    Re: Pivot Count Unique values - Excel 2010

    Thanks a lot, thats awesome.
    Reply With QuoteRegister To Reply
  4. 01-27-2017, 10:32 AM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,666

    Re: Pivot Count Unique values - Excel 2010

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count unique values in pivot table - excel 2010
    By Tino XXL in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-20-2014, 04:45 AM
  2. [SOLVED] count unique values in pivot table
    By Fugazi_Dent in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2013, 11:58 AM
  3. [SOLVED] Count only unique values in pivot table
    By Leahmaria26 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-25-2013, 08:56 PM
  4. Count unique values with pivot table
    By distribution master in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-07-2013, 10:47 AM
  5. Count Unique values with 2 criteria - Excel 2010
    By mmaya4 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 06-22-2012, 07:28 AM
  6. Count unique values in pivot table
    By karinos57 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-10-2009, 12:03 PM
  7. [SOLVED] Count unique values - Pivot Table
    By Thomas Mueller in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-02-2005, 06:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1