Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Count Unique Customers by Class and then Consolidated

    In column A i have the names of customers separated by contract types. Some customers are listed in more than one contract type. For example, column A, rows 1-35 contain the names of customers having contract type I. In rows 36-78, the names of customers having contract type II, some of these latter customers also appear in rows 1-35. Finally, in rows 79-140, the names of customers having contract type III, again, some of these may also appear (more than once) in rows 1-78. In columns B-M, rows 1-140 are the monthly quantity of a commodity purchases by the respective sustomer under each contract type.

    Ehat I would like is in row 142, columns B-M is list the number of "unique" (i. e., no duplicates) customers purchasing under contract type I. In row 143, columns B-M list the number of "unique" (i. e., no duplicates) customers purchasing under contract type II, and the same for contract type II in the same columns in row 144. Finally, in row 145, columns B-M list the number of truly unique customers doing business each month regardless of contract type. For example, if a customer in a given month purchased goods under contract types I and II, he would only be counted once.

    Any ideas?

    Thanks in advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A pivot table might work. An example workbook would be helpful as well for us to play...

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Here you go Steve
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Is this what you're looking for? =COUNTIFS($B$1:$B$50,"I",C$1:C$50,"<>0")

    (change the 50 to however many rows you have of the three types, and change the "I" to "II" and then "III" in the two rows that are to count those types.)

    Then, fill across to column M.

    Oops, guess not...I reread your post and you're looking for unique customer counts.
    As Steve said, maybe a pivot table is best. Rethinking.
    Last edited by kweaver; 2013-03-19 at 10:19.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for your reply, Kweaver. I think we almost have it, except I am looking for a "unique" counting of customers in each classification. Your formula includes duplicates in its results. ANy other ideas?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    See the answer where you cross-posted. http://eileenslounge.com/viewtopic.p...410d2494295af9

    Also see this link about cross-posting: http://www.excelguru.ca/content.php?184

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Steve. I was under a time constraint and didn't hear anything back. I was just getting ready to post Han's solution, but you beat me to it.
    Thanks for your help.

Posting Permissions

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