how many staff have 1 skill, how many staff have 2 skills, etc.

[Guest]

dear excel lovers,

one spreadsheet (2000 records), 2 columns, staff_id (700 unique records) and
skill_id (10 unique records). one staff can have one or more skill and I need
to know how many staff have 1 skill, how many staff have 2 skills, etc.

somehow it seems easy on paper but I can't found anything on the access or
excekl forums because I don't know what to look for!!

help, I need somebody, help, ...
chris90

 

[Guest]

With your list in columns A and B, with headings in A1 and B1, try this
formula:

For staff with 3 skills:
D1: =SUMPRODUCT((COUNTIF($A$2:$A$2000,$A$2:$A$2000)=3)/3)

For staff with 2 skills:
E1: =SUMPRODUCT((COUNTIF($A$2:$A$2000,$A$2:$A$2000)=2)/2)

Does that help?

 

[Guest]

simple and elegant, it is working like a charm
many thanks you saved my day

chris90

 

[Guest]

You're very welcome. I'm glad I could help.