Countifs, Sumproduct...?

[Skovgaard]

Hi,

Sorry if there is another thread about this, but I can't find any, or I don't know the right search criteria

Please see below table.
Do you know a formula to calculate how many times two specific companies, has been working on the same project?

In below example the results should be:

A+B = 2 (in project 10+11)
A+D = 1 (in project 11)

Project:	Company:
10	A
10	B
10	C
11	A
11	B
11	D
12	A
12	C

<tbody>
</tbody>

/Skovgaard

 

[Marcelo Branco]

Maybe..

	A​	B​	C​	D​	E​	F​
1​	Project​	Company​		Company​	Company​	Result​
2​	10​	A​		A​	B​	2​
3​	10​	B​		A​	D​	1​
4​	10​	C​
5​	11​	A​
6​	11​	B​
7​	11​	D​
8​	12​	A​
9​	12​	C​

Array formula in F2 copied down
=SUM(--ISNUMBER(MATCH(IF(B$2:B$9=D2,A$2:A$9,""),IF(B$2:B$9=E2,A$2:A$9),0)))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Skovgaard]

Thanks, works perfectly!

/Skovgaard

 

[Marcelo Branco]

You are welcome. Glad to help.

M.