Countifs, Sumproduct...?

Skovgaard

New Member
Joined
Oct 18, 2013
Messages
39
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:
10A
10B
10C
11A
11B
11D
12A
12C

<tbody>
</tbody>


/Skovgaard
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 

Forum statistics

Threads
1,089,635
Messages
5,409,449
Members
403,264
Latest member
naturally_data

This Week's Hot Topics

Top