Separate the boys from the girls?

I would like a simple function that can help keep a dynamic count of males
and females assigned to a section. For example we will say there is a list
of 100 employees. 50 males, 50 females. I could put them on a list on sheet
two, possibly in two columns. On sheet one I could list the exact employee
names for employees assigned to different areas. Below each area I would
have a count of names to list how many males and how many females were
assigned to the area based upon the two lists placed on sheet 2.

If possible I would like to accomplish this with no VBA or helper columns
because others would be using the schedule and making assignments every day.
Is this practical? I know how to work with Excel functions but can not come
up with a combination that would accomplish this. Exact matching of names
will work because we always copy and paste names when moving and assigning
employees in the schedule.
Quin

Examples?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Quin" wrote in message
...
I would like a simple function that can help keep a dynamic count of males
and females assigned to a section. For example we will say there is a
list
of 100 employees. 50 males, 50 females. I could put them on a list on
sheet
two, possibly in two columns. On sheet one I could list the exact
employee
names for employees assigned to different areas. Below each area I would
have a count of names to list how many males and how many females were
assigned to the area based upon the two lists placed on sheet 2.

If possible I would like to accomplish this with no VBA or helper columns
because others would be using the schedule and making assignments every
day.
Is this practical? I know how to work with Excel functions but can not
come
up with a combination that would accomplish this. Exact matching of names
will work because we always copy and paste names when moving and assigning
employees in the schedule.
Quin

On Sat, 29 May 2010 04:37:01 -0700, Quin
wrote:

I would like a simple function that can help keep a dynamic count of males
and females assigned to a section. For example we will say there is a list
of 100 employees. 50 males, 50 females. I could put them on a list on sheet
two, possibly in two columns. On sheet one I could list the exact employee
names for employees assigned to different areas. Below each area I would
have a count of names to list how many males and how many females were
assigned to the area based upon the two lists placed on sheet 2.

If possible I would like to accomplish this with no VBA or helper columns
because others would be using the schedule and making assignments every day.
Is this practical? I know how to work with Excel functions but can not come
up with a combination that would accomplish this. Exact matching of names
will work because we always copy and paste names when moving and assigning
employees in the schedule.
Quin


Assuming your males names are in cells A1:A100 in Sheet2 and that the
females names are in cells B1:B100 in Sheet2.

If your "area list" in Sheet1 is in cells A1:A20 then you may try the
following formulas:

Note: These are array formulas that must be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

For the number of males:
=SUM(--(MMULT(--(TRANSPOSE(A1:A20)""),--(A1:A20=TRANSPOSE(Sheet2!A1:A100)))))

For the number of females:
=SUM(--(MMULT(--(TRANSPOSE(A1:A20)""),--(A1:A20=TRANSPOSE(Sheet2!B1:B100)))))

If there are no blank cells in the list A1:A20 this can be simplified
a bit:

For the number of males:
=SUM(--(A1:A20=TRANSPOSE(Sheet2!A1:A100)))

For the number of females:
=SUM(--(A1:A20=TRANSPOSE(Sheet2!B1:B100)))

Note: These are array formulas that must be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke

Your Solution is exactly what I was looking for and it works great. The
Control+Shift+Enter instruction was something new to me. I was not sure how
to make it work as I did not want to type the formula, but paste if directly
from your post instead. I was not successful to just paste and then use
Control+Shift+Enter but I found if I paste the function into the cell, then
delete any part of the formula and then retype it as though I had typed the
entire thing I then could use Control+Shift+Enter to activate it in the cell.
(There is no doubt an easier way)

I knew it was right when I got the curly brackets around it. I also
discovered it is not too smart to try to put the formula where it interfered
with the “area list� in this case A1:A20.

After I worked out those small details I had great success. I appreciate
the time you took to help me out with it. This will help me get started
learning about Array Formulas.
Quin