+ Reply to Thread
Results 1 to 11 of 11

Counting Uniqe values with criteria

  1. #1
    Registered User
    Join Date
    01-21-2014
    Location
    Mass
    MS-Off Ver
    Excel 2010
    Posts
    8

    Angry Counting Uniqe values with criteria

    I am trying to identify the unique customers that have purchased a product. I have tried multiple formulas (see below) however I cannot get any of them to work correctly with multiple criteria. Below is my data set:

    Data Set
    Item Customer
    a 8089
    a 8089
    b 8089
    c 8089
    d 8089
    a 8089
    a 8089
    a 8089
    a 9337
    a 13514
    b 13514
    a 26048
    a 26992
    a 29684
    c 29684
    a 29984
    a 30926




    The resulting answers should be:

    Item Distinct Customers
    a 8
    b 2
    c 2
    d 1


    The challenge is applying this to a very large data set (100k+ lines) and I hate "breaking excel" as I do far to often by not using streamlined formulas.
    Last edited by SFMASS; 01-21-2014 at 04:13 PM. Reason: bad formatting

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Counting Uniqe values with criteria

    I would use a pivot table...that's what they do.....if you need help on that let me know.
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Registered User
    Join Date
    01-21-2014
    Location
    Mass
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Counting Uniqe values with criteria

    The Challege with the pivot is it does not count unique instances. I am trying to apply this to a large data set to see exceptional sales where one customer is driving sales on a particular item

  4. #4
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Counting Uniqe values with criteria

    right....I caught that....you could use the remove duplication using the column B as a sort then pivot....I guess it depends how often you have to do this..

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Counting Uniqe values with criteria

    are each like a 8089 all in the same cell?
    are you not able to use the filter >> advanced to post to another location unique values only?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  6. #6
    Registered User
    Join Date
    01-21-2014
    Location
    Mass
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Counting Uniqe values with criteria

    man i wish life was that easy...there is multiple lines of data that i need as well the unique customer is just one factor. For example i am also summerizing total sales, qty etc so i need each line of data (completed using sunifs and average ifs) the hang up is how to sum unique customers

  7. #7
    Registered User
    Join Date
    01-21-2014
    Location
    Mass
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Counting Uniqe values with criteria

    wow learned something new there...thank you sambo...the challenge is (judge and i were discussing) is i need all the data to be in the query. on my summary sheet that ultimately goes into a pivot i need to add a field that indicates unique customers

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Counting Uniqe values with criteria

    I'm having trouble following. If you can create a unique grouping of everything you can then set up another area where you've summed each unique item and work from that. Maybe a sample spreadsheet with confidential / sensitive info removed would help. Go advanced and manage attachments for that.

  9. #9
    Registered User
    Join Date
    01-21-2014
    Location
    Mass
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Counting Uniqe values with criteria

    Here is a sample spreadsheet. I am trying to find a formula that will tell # of unique customers that bought the item.

    Sample Unique Customer.xlsx

  10. #10
    Registered User
    Join Date
    01-21-2014
    Location
    Mass
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Counting Uniqe values with criteria

    so i figured it out after banging my head for 6 hours...turns out it is an array formula that i saw on another post. here is the formula:

    =COUNT(1/FREQUENCY(IF(Table1811[Item]=[@Item],IF(Table1811[Customer ID]<>"",Table1811[Customer ID])),IF(Table1811[Item]=[@Item],IF(Table1811[Customer ID]<>"",Table1811[Customer ID]))))

  11. #11
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Counting Uniqe values with criteria

    always fun to figure it out on your own....way to persevere...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 12-07-2011, 05:25 PM
  2. Pull out uniqe Values.
    By Ipsl in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-31-2008, 12:18 AM
  3. Counting Unique Values Given Criteria
    By Domenic in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 09:05 AM
  4. Counting Unique Values Given Criteria
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  5. Counting Unique Values Given Criteria
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1