If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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 
Ads 
#2




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




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




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 > > > 
Thread Tools  
Display Modes  


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 12:45 PM 