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

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

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

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?

--
Regards,
Ron


"ch90" wrote:

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

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

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

chris90

"Ron Coderre" wrote:

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?

--
Regards,
Ron


"ch90" wrote:

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

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

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

--
Regards,
Ron


"ch90" wrote:

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

chris90

"Ron Coderre" wrote:

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?

--
Regards,
Ron


"ch90" wrote:

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