+ Reply to Thread
Results 1 to 2 of 2

When Duplicates exist in one Column, create a count based on Arguments in other columns...

  1. #1
    Registered User
    Join Date
    06-19-2014
    Location
    Wilmington, NC
    MS-Off Ver
    2010
    Posts
    1

    When Duplicates exist in one Column, create a count based on Arguments in other columns...

    I have a list of data that I'm trying to write a formula for to create a specific count. In one column, I have client id's which is sorted by the client id in order to show where they showed up more than once. What I want to do is create a formula that will count how many times a specific combination occurs in another column if excel see's a match in the client id column.

    So for example, my data might look like this:

    Client ID Result
    123 food
    123 gas
    127 gas
    127 food
    131 drink
    131 food
    149 drink
    149 gas
    151 drink
    151 food
    151 gas

    Assume Client ID = Column A and Result = Column B. What I want excel to do is tell me how many times the same Client ID in Column A has a resulting combination of both Food and Gas. So in the example above, the formula should be able to run a count with an answer of 3 since clients (123, 127 and 151) all have a result of both Food and Gas.

    Any help is appreciated here!

    Thank you!

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: When Duplicates exist in one Column, create a count based on Arguments in other column

    Try this one

    =SUMPRODUCT((A2:A12<>"")/COUNTIF(B2:B12,B2:B12&""))

    A
    B
    C
    1
    Client ID Result
    2
    123
    food
    3
    3
    123
    gas
    4
    127
    gas
    5
    127
    food
    6
    131
    drink
    7
    131
    food
    8
    149
    drink
    9
    149
    gas
    10
    151
    drink
    11
    151
    food
    12
    151
    gas
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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. Merge Columns B,C,D,E and F, If duplicates exist in Column A
    By julia81 in forum Excel Programming / VBA / Macros
    Replies: 84
    Last Post: 03-25-2014, 03:37 PM
  2. [SOLVED] Count duplicates in column based on criteria
    By roxylai in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-23-2013, 07:32 PM
  3. Create a new column based on count of two other columns
    By ftcnt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2012, 09:10 AM
  4. [SOLVED] If Duplicates Exist, Create a New Row
    By alecmat in forum Excel General
    Replies: 6
    Last Post: 03-27-2012, 10:23 AM
  5. Comparing Two columns to create column with duplicates
    By braves1t in forum Excel General
    Replies: 6
    Last Post: 04-29-2010, 08:31 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