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:













Lets say above table is in cells A1 to B7

Cell D1 to F5 is filled like this:










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.

skolb said:

I hope someone can help me.

Click to expand...

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

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

Hello Navic,


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


Kind regards,
sKoLb