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)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Project:[/TD]
[TD]Company:[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]


/Skovgaard

 

[Marcelo Branco]

Maybe..


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Project​

[/td][td]

Company​

[/td][td][/td][td]

Company​

[/td][td]

Company​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

10​

[/td][td]

A​

[/td][td][/td][td]

A​

[/td][td]

B​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

10​

[/td][td]

B​

[/td][td][/td][td]

A​

[/td][td]

D​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

10​

[/td][td]

C​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

11​

[/td][td]

A​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

11​

[/td][td]

B​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

11​

[/td][td]

D​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

12​

[/td][td]

A​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

12​

[/td][td]

C​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.