Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 27th 05, 02:31 PM
ch90
 
Posts: n/a
Default 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   Report Post  
Old October 27th 05, 03:13 PM
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Old October 27th 05, 03:29 PM
ch90
 
Posts: n/a
Default 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   Report Post  
Old October 27th 05, 03:52 PM
Ron Coderre
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
(A = 3) = ... :( JemyM New Users to Excel 5 September 5th 05 01:30 PM
Basic skills testing Rob Excel Discussion (Misc queries) 0 August 19th 05 08:03 PM
Allocate workload evenly to different staff KC Excel Worksheet Functions 2 February 19th 05 11:45 AM


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

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017