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

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
>

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
> >

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
> > >