Results 1 to 3 of 3

Thread: Sum how many times a combination is made

  1. #1

    Sum how many times a combination is made



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all,

    I'm having trouble making a formula that counts the times a specific combination is present. I have searched the internet but can't figure out how to make this work. It's a combination of formulas probably. I hope you can help me. I have no experience with VBA code in Excel so I'd rather not use that.

    The scenario:

    I have a long table with data of which people need to be present in which week like this for a whole year:

    Week Person
    1 A
    1 B
    1 C
    2 A
    2 B
    2 D












    Lets say above table is in cells A1 to B7

    Cell D1 to F5 is filled like this:

    Person 1 Person 2 Amount
    A B
    A C
    A D









    Next to A B (in cell F2) I would like to sum the times person A and B are present together.

    So in this example:
    Person A and B are present together in week 1 and 2 meaning i would like to see 2 in the Amount column.
    Person A and D are only present together once in week 2 so i would like to see 1 in the Amount column next to A and D in cell F4.


    I other words, I need to find matches where person B is present in the same week as person A and then sum those matches.

    I hope someone can help me.

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by skolb View Post
    I hope someone can help me.
    You should set up an attachment to a better understanding. What is the result of A-C?
    If I understood you well, here's an idea with extra columns

    In cell F2 set this formula
    Code:
    =IF(AND(COUNTIF($Z$2:$Z$19;1&D2)>0;COUNTIF($Z$2:$Z$19;1&E2)>0)=TRUE;1;0)+IF(AND(COUNTIF($AA$2:$AA$19;2&D2)>0;COUNTIF($AA$2:$AA$19;2&E2)>0)=TRUE;1;0)+IF(AND(COUNTIF($AB$2:$AB$19;3&D2)>0;COUNTIF($AB$2:$AB$19;3&E2)>0)=TRUE;1;0)+IF(AND(COUNTIF($AC$2:$AC$19;4&D2)>0;COUNTIF($AC$2:$AC$19;4&E2)>0)=TRUE;1;0)
    I believe that this problem can be solved easily, but there it was my idea to solve your problems. I hope that will help you in your the task to solve problems.
    Please see my attachment
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Hello Navic,


    Thank you for your reply. With your example and explanation it works perfectly.


    Kind regards,
    sKoLb

Posting Permissions

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