Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help: Multiple Criteria
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help: Multiple Criteria
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(--(D1:D699="Male"),--(C1:C699="X")) to count the males, and an obvious change for the females. Hope this helps. Pete On May 2, 11:44*pm, Chris McKenzie wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help: Multiple Criteria
I didn't think of that. Thank you very much.
"Pete_UK" wrote: 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(--(D1:D699="Male"),--(C1:C699="X")) to count the males, and an obvious change for the females. Hope this helps. Pete On May 2, 11:44 pm, Chris McKenzie wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help: Multiple Criteria
You're welcome, Chris - thanks for feeding back.
Pete On May 5, 7:38*am, Chris McKenzie wrote: I didn't think of that. Thank you very much. "Pete_UK" wrote: 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(--(D1:D699="Male"),--(C1:C699="X")) to count the males, and an obvious change for the females. Hope this helps. Pete On May 2, 11:44 pm, Chris McKenzie wrote: 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!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |