+ Reply to Thread
Results 1 to 10 of 10

Counting unique instances above a certain quantity

  1. #1
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Counting unique instances above a certain quantity

    Hello. If you have a list of customers (for example, A, A, B, B, B, C, C, C, C) how can you can count the numbers of customers with over a certain amount of entries. So, for example if a customer is in 4 rows, I want that to be counted as 1, but not if a customer just has three rows. It's sort of a mix of unique count plus a conditional.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Counting unique instances above a certain quantity

    Hi,

    With your list of customers in A1:A9 and the customer you want to count in C1 maybe,

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: Counting unique instances above a certain quantity

    Thanks for responding but I don't think I explained myself clearly. Let's say you have a list of customers and some of those customers appear multiple times in a list. You can use a formula in excel to get a unique count of customers, so just counting the customers and not the amount of times they appear in the list. I want to do something similar BUT I want to count the unique customers that appear more than 3 (or x) times in the list. Make sense?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Counting unique instances above a certain quantity

    Quote Originally Posted by ammartino44 View Post
    Make sense?
    Sadly not, but maybe that's me and it's late. However if you could put a small table together with various permutations of customers in different columns and manually add the result you wish to see for each set of customers above each column then I'll have a stab and see.

  5. #5
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: Counting unique instances above a certain quantity

    The customers would all be in the same column, unless you are proposing something different. Here is a simple spreadsheet with the result that I want (obviously, the spreadsheet I'm looking at has thousands of rows).
    Attached Files Attached Files

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Counting unique instances above a certain quantity

    A
    B
    C
    D
    E
    F
    2
    Customer
    Year
    Customer
    Count >=3
    3
    A
    1
    A
    4
    E3: =SUMPRODUCT(--(COUNTIF(A3:A19, D3:D8)>=3))
    4
    A
    2
    B
    5
    A
    3
    C
    6
    A
    4
    D
    7
    B
    1
    E
    8
    B
    2
    F
    9
    B
    3
    10
    C
    1
    11
    C
    2
    12
    D
    1
    13
    D
    2
    14
    D
    3
    15
    E
    1
    16
    E
    2
    17
    F
    1
    18
    F
    2
    19
    F
    3
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: Counting unique instances above a certain quantity

    @shg. What are you doing with column D. I don't understand.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Counting unique instances above a certain quantity

    I copied all the customers from col A and removed duplicates.

  9. #9
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: Counting unique instances above a certain quantity

    And how would you do that with formulas? I have 50,000 rows haha. I ended up using pivot tables but I'd still be interested in learning how to do it with formulas. Thanks.

  10. #10
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Counting unique instances above a certain quantity

    Hi,

    you could try array formulas, but 50.000 rows are quite challenging to be processed

    In D2 -to extract the unique list- confirmed with control+shift+enter before to be copied below

    Please Login or Register  to view this content.
    Pivot table, "remove duplicates" are for sure far better ways to achieve it.

    Cheers
    Last edited by canapone; 10-03-2014 at 03:06 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

+ 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. [SOLVED] Counting unique text instances with conditions
    By kazaly in forum Excel General
    Replies: 8
    Last Post: 11-19-2012, 11:44 PM
  2. Quantity Counting
    By H0dg3 in forum Excel General
    Replies: 1
    Last Post: 03-24-2011, 02:04 PM
  3. Counting unique instances in an array.
    By Jerry McM in forum Excel General
    Replies: 2
    Last Post: 02-13-2009, 05:58 PM
  4. count quantity of each unique value
    By Mungyun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2008, 12:29 PM
  5. counting unique instances of text in a list
    By WadeSansing in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-01-2005, 01:57 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1