Remember Me?

#1
October 27th 05, 02:31 PM
 ch90 Posts: n/a
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

#2
October 27th 05, 03:13 PM
 Ron Coderre Posts: n/a
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

#3
October 27th 05, 03:29 PM
 ch90 Posts: n/a
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

#4
October 27th 05, 03:52 PM
 Ron Coderre Posts: n/a
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post JemyM New Users to Excel 5 September 5th 05 01:30 PM Rob Excel Discussion (Misc queries) 0 August 19th 05 08:03 PM KC Excel Worksheet Functions 2 February 19th 05 11:45 AM

All times are GMT +1. The time now is 07:40 PM.