1. ## Sum how many times a combination is made

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. Originally Posted by skolb
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.

3. Hello Navic,

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
•