Counting competencies in different working areas

[Sir Vili]

Hello every body.

I have people who has competencies and validation to work in different areas. I want to calculate how many for example have validation to work in Arrival Service + Check-in. How many in Arrival Service + Check-in + Gate and so on. Any ideas for formulas?

Book3
	A	B	C	D	E
1		ArrivalService	Check-in	Gate	Lounge
2	Anja		x	x
3	Inca	x	x	x	x
4	Kari	x	x
5	Matti	x	x	x	x
6	Milla		x	x	x
7	Nuutti	x		x
8
9		Howmanyarevalidwith:
10	1.	ArrivalService+Check-in
11	2.	ArrivalService+Check-in+Gate
12	3.	ArrivalService+Check-in+Gate+Lounge
13	4.	Check-in+Gate
14	5.	Check-in+Gate+Lounge
15	6.	Gate+Lounge
Sheet1

Sir Vili.

 

[PaddyD]

On the assumption that all compitencies are marked with an 'x', you could use sumproduct for this:

with data as provided

=SUMPRODUCT((B2:B7="x")*(C2:C7="x"))

would provide a count of people qualified in both arrivals * check in. The generalisation to other conditions should be straightforward...

Paddy

 

[Sir Vili]

Hello PaddyD.

Easy and clean solution. Thanks a lot.

Best Regards Sir Vili.