+ Reply to Thread
Results 1 to 10 of 10

Counting unique cells - totally excluding duplicate values

  1. 06-14-2013, 04:07 PM #1
    ChanceLipscomb
    ChanceLipscomb is offline
    Registered User
    Join Date
    06-14-2013
    Location
    America
    MS-Off Ver
    Excel 2007
    Posts
    2

    Counting unique cells - totally excluding duplicate values

    I have read a lot in here about counting unique cells. However, I need to completely disregard duplicate values when counting.

    For example, column A is a list of customer numbers. Each row of data represents an order they have placed. I need to count the number of customers who have only made one order. With the array formulas I am reading about for counting unique values, even the customers who have ordered more than once will be counted once.

    Column A
    655
    767
    878
    655
    456

    Using the "count unique values" formula I have seen on many other threads, the count for the above data would be 4. I need to completely disregard the "655" and end up with a total count of 3 completely unique values.

    Any help on this would be greatly appreciated.
    Please let me know if I can clarify anything, or if you need any more information.
    Reply With QuoteRegister To Reply
  2. 06-14-2013, 04:15 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting unique cells - totally excluding duplicate values

    Try this array formula**:

    =SUM(IF(FREQUENCY(MATCH(A2:A6,A2:A6,0),ROW(A2:A6)-ROW(A2)+1)=1,1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Assumes no empty cells within the range.

    I'm also assuming that these are just fictitious customer numbers. The real customer numbers are more than 3 digit numbers?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 06-14-2013, 04:24 PM #3
    ChanceLipscomb
    ChanceLipscomb is offline
    Registered User
    Join Date
    06-14-2013
    Location
    America
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Counting unique cells - totally excluding duplicate values

    Wow. Thanks!
    It seems to be working perfectly.
    I just looked at the formula. Just so I unde, it is asking for unique values, and then subtracting the values that occur more than once? SOmething to that effect?
    At any rate, very clever. Much obliged!
    Reply With QuoteRegister To Reply
  4. 06-14-2013, 04:34 PM #4
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,292

    Re: Counting unique cells - totally excluding duplicate values

    =SUM(IF(FREQUENCY(A1:A16;A1:A16)>0;1))
    Or this array formula if there are empty cells within the range
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong
    Reply With QuoteRegister To Reply
  5. 06-14-2013, 04:36 PM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting unique cells - totally excluding duplicate values

    It does a sort of "countif" based on the matching row numbers of the data. Then it counts how many of those "countifs" evaluate to 1 (totally unique entries).

    Thanks for the feedback!
    Reply With QuoteRegister To Reply
  6. 06-14-2013, 04:40 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting unique cells - totally excluding duplicate values

    If the customer numbers really are just 3 digit numbers as shown in your sample then you can use this much shorter normally entered formula:

    =SUM(--(FREQUENCY(A2:A6,A2:A6)=1))
    Reply With QuoteRegister To Reply
  7. 06-14-2013, 10:36 PM #7
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Counting unique cells - totally excluding duplicate values

    =SUMPRODUCT(--(COUNTIF(A2:A6,A2:A6)=1))

    Normally Enter
    Reply With QuoteRegister To Reply
  8. 06-15-2013, 05:33 PM #8
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting unique cells - totally excluding duplicate values

    Just for the fun of it I tested the calculation time of these 3 formulas on 100 rows of data using Charles Williams range timer method (average of 5 calculations per formula).

    Array formula:

    =SUM(IF(FREQUENCY(MATCH(A2:A101,A2:A101,0),ROW(A2:A101)-ROW(A2)+1)=1,1))

    =SUM(--(FREQUENCY(A2:A101,A2:A101)=1))

    =SUMPRODUCT(--(COUNTIF(A2:A101,A2:A101)=1))

    On my machine the array formula was about 3.5 times faster to calculate than the SUMPRODUCT(--(COUNTIF formula. And, if the data really is numeric values then the SUM(--(FREQUENCY formula is about 2.5 times faster to calculate than the array formula.

    EDIT: The SUM(--(FREQUENCY formula will only work on numeric data.
    Last edited by Tony Valko; 06-15-2013 at 05:35 PM.
    Reply With QuoteRegister To Reply
  9. 05-26-2016, 12:14 PM #9
    kimberlieh
    kimberlieh is offline
    Registered User
    Join Date
    08-27-2012
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Counting unique cells - totally excluding duplicate values

    This is the first time I have tried to ask a question on this forum, but I am excited after trying your 06-14-2013 post to count unique cells excluding duplicate values. I tried it and it worked...well almost!

    Is it possible to add an "IF, AND" to the formula, and if the result is false, then the formula will just add the values?

    My formula now looks like this; =SUM(IF(FREQUENCY(MATCH('Cert Report'!M7,Time!D:D,0),ROW('Cert Report'!M7)-ROW('Cert Report'!M7)+1)=1,1)).

    Thank you
    Reply With QuoteRegister To Reply
  10. 05-26-2016, 12:44 PM #10
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Counting unique cells - totally excluding duplicate values

    kimberlieh welcome to the forum

    Im really sorry to have to do this on your 1st post, but...
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    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)

Tags for this Thread

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