Need Help: Multiple Criteria

[Chris McKenzie]

I have a list of urinalysis tests that I've just been told needs to be
differentiated by male and female. Instead of going back through and trying
to figure out which donor is male and female, I've decided to determine the
count based on the gender of the collector (since the collector has the be
the same gender as the donor and there are far fewer collectors than donors),
but I would like to create a formula so I do not have to manually change the
formula whenever staff leave or come on. They also have to be part of our
Program (marked with an "x") and not a Random. So I have my main chart:

A (Donor) B (Collector) C. (Program (X) or
Random (Blank)
A1. Donor #1 B1. Collector #1 C1. X
A2. Donor #2 B2. Collector #1 C2.
A3. Donor #1 B3. Collector #2 C3. X
A4. Donor #3 B4. Collector #3 C4. X
Down to 699

So then I have colums:
D. Staff E. Gender
D1. Collector #1 E1. Male
D2. Collector #2 E2. Male
D3. Collector #3 E3. Female
Down to 30

So, basically, I want to find a count of the total number of tests of males,
based on the gender of the collector.

So, something like =SUMPRODUCT(--(B1:B699={What...?}),--(C1:C699="X")). If
anyone could help with the formula to slide into the first part, I would
appreciate it, since it would save a lot of work. Or if you could let me know
if I'm completely off base and offer suggestions, thanks!

 

[Pete_UK]

It would be a bit simpler if you were to insert a new column D and put
this formula in D1:

=VLOOKUP(B1,E$1:F$30,2,0)

Copy this down, and it will fill column D with Male or Female.

Then your counting formula can be:

=SUMPRODUCT(--(D1699="Male"),--(C1:C699="X"))

to count the males, and an obvious change for the females.

Hope this helps.

Pete

 

[Chris McKenzie]

I didn't think of that. Thank you very much.

It would be a bit simpler if you were to insert a new column D and put
this formula in D1:

=VLOOKUP(B1,E$1:F$30,2,0)

Copy this down, and it will fill column D with Male or Female.

Then your counting formula can be:

=SUMPRODUCT(--(D1699="Male"),--(C1:C699="X"))

to count the males, and an obvious change for the females.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Chris - thanks for feeding back.

Pete