+ Reply to Thread
Results 1 to 9 of 9

Count duplicates in column based on criteria

  1. 10-22-2013, 04:32 PM #1
    roxylai
    roxylai is offline
    Registered User
    Join Date
    10-22-2013
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Count duplicates in column based on criteria

    I am trying to write a formula that will look at a column of names and count how many of those names are repeated more than 4 times. For example (would return a 2):
    Apple
    Apple
    Banana
    Orange
    Banana
    Banana
    Orange
    Apple
    Grapes
    Banana
    Grapes
    Apple
    Apple
    Banana

    Thanks!!
    Reply With QuoteRegister To Reply
  2. 10-22-2013, 04:40 PM #2
    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
    53,048

    Re: Count duplicates in column based on criteria

    Hi and welcome to the forum

    This should give you a start on what you want...
    =COUNTIF($A$1:$A$14,A1)>4

    THis will produce a TRUE or FALSE, and from that you can expand
    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
  3. 10-22-2013, 04:40 PM #3
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count duplicates in column based on criteria

    Hi,

    One way:

    =SUMPRODUCT(--(FREQUENCY(MATCH(A2:A15,A2:A15,0),ROW(A2:A15)-MIN(ROW(A2:A15))+1)>4))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  4. 10-22-2013, 04:42 PM #4
    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: Count duplicates in column based on criteria

    Try this...

    =SUMPRODUCT(--(FREQUENCY(MATCH(A2:A15,A2:A15,0),ROW(A2:A15)-ROW(A2)+1)>4))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  5. 10-23-2013, 02:29 PM #5
    roxylai
    roxylai is offline
    Registered User
    Join Date
    10-22-2013
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Count duplicates in column based on criteria

    Thanks for your ideas everyone!
    XOR LX - I tried it and it seemed to return 15.....
    Tony - I tried it and it seemed to return 0
    Reply With QuoteRegister To Reply
  6. 10-23-2013, 02:37 PM #6
    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
    53,048

    Re: Count duplicates in column based on criteria

    I just tried Tony's and it gave me 2. Make sure the range in the formula matches your range in your worksheet
    Reply With QuoteRegister To Reply
  7. 10-23-2013, 03:53 PM #7
    roxylai
    roxylai is offline
    Registered User
    Join Date
    10-22-2013
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Count duplicates in column based on criteria

    You are right FDibbins, I must have missed something small, it works now. Thanks everyone!!
    Reply With QuoteRegister To Reply
  8. 10-23-2013, 03:57 PM #8
    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
    53,048

    Re: Count duplicates in column based on criteria

    Happy to help
    Reply With QuoteRegister To Reply
  9. 10-23-2013, 07:32 PM #9
    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: Count duplicates in column based on criteria

    Good deal. Thanks for the feedback!
    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 with multiple criteria without duplicates based on a different column
    By antagonanin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-14-2013, 09:46 AM
  2. Count unique values (ignore duplicates) with multiple other column criteria
    By noofo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2013, 07:50 PM
  3. [SOLVED] Count without duplicates based on multiple criteria from different cells
    By perryadam in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2013, 12:54 PM
  4. [SOLVED] Removing Duplicates based on column values and criteria
    By viduran88 in forum Excel General
    Replies: 9
    Last Post: 04-04-2013, 11:37 AM
  5. Count Unique Occurrences in one column based on criteria another column
    By ltsolis in forum Excel General
    Replies: 5
    Last Post: 09-18-2011, 10:44 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