Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF or SUMPRODUCT
Not sure which way to go on this. I have three columns (Race, Rank, Gender)
and I need to count how many white,sgt,males there are and put it in one cell in another worksheet in the same book. Have search the board and everything I've tried has not worked. If you could help me with this, I can replace what I need. Race column is D2:D4000 Rank Column is E2:E4000 Gender Column is G2:G4000 Thanks! Dan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF or SUMPRODUCT
Try this:
=SUMPRODUCT(--(D2:D4000="white"),--(E2:E4000="sgt"),--(G2:G4000="male")) Better to use cells to hold the criteria: A1 = white B1 = sgt C1 = male =SUMPRODUCT(--(D2:D4000=A1),--(E2:E4000=B1),--(G2:G4000=C1)) -- Biff Microsoft Excel MVP "SFC Traver" wrote in message ... Not sure which way to go on this. I have three columns (Race, Rank, Gender) and I need to count how many white,sgt,males there are and put it in one cell in another worksheet in the same book. Have search the board and everything I've tried has not worked. If you could help me with this, I can replace what I need. Race column is D2:D4000 Rank Column is E2:E4000 Gender Column is G2:G4000 Thanks! Dan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF or SUMPRODUCT
Thanks. It worked. I tried to make it simplier for whomever takes over for me
by assigning names to the cell ranges and I keep getting the #NUM error. I named the D column "REDCAT", E column "Grade", G column "Gender" and then named each cell like you recommended (white, black, sgt, ssg, etc). Then I replaced the formula you sent and it's not working. Can I do this? =SUMPRODUCT(--(REDCAT=WHITE),--(Gender=M),--(Grade=Jr NCO)) "T. Valko" wrote: Try this: =SUMPRODUCT(--(D2:D4000="white"),--(E2:E4000="sgt"),--(G2:G4000="male")) Better to use cells to hold the criteria: A1 = white B1 = sgt C1 = male =SUMPRODUCT(--(D2:D4000=A1),--(E2:E4000=B1),--(G2:G4000=C1)) -- Biff Microsoft Excel MVP "SFC Traver" wrote in message ... Not sure which way to go on this. I have three columns (Race, Rank, Gender) and I need to count how many white,sgt,males there are and put it in one cell in another worksheet in the same book. Have search the board and everything I've tried has not worked. If you could help me with this, I can replace what I need. Race column is D2:D4000 Rank Column is E2:E4000 Gender Column is G2:G4000 Thanks! Dan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF or SUMPRODUCT
Answered in your other post.
-- Biff Microsoft Excel MVP "SFC Traver" wrote in message ... Thanks. It worked. I tried to make it simplier for whomever takes over for me by assigning names to the cell ranges and I keep getting the #NUM error. I named the D column "REDCAT", E column "Grade", G column "Gender" and then named each cell like you recommended (white, black, sgt, ssg, etc). Then I replaced the formula you sent and it's not working. Can I do this? =SUMPRODUCT(--(REDCAT=WHITE),--(Gender=M),--(Grade=Jr NCO)) "T. Valko" wrote: Try this: =SUMPRODUCT(--(D2:D4000="white"),--(E2:E4000="sgt"),--(G2:G4000="male")) Better to use cells to hold the criteria: A1 = white B1 = sgt C1 = male =SUMPRODUCT(--(D2:D4000=A1),--(E2:E4000=B1),--(G2:G4000=C1)) -- Biff Microsoft Excel MVP "SFC Traver" wrote in message ... Not sure which way to go on this. I have three columns (Race, Rank, Gender) and I need to count how many white,sgt,males there are and put it in one cell in another worksheet in the same book. Have search the board and everything I've tried has not worked. If you could help me with this, I can replace what I need. Race column is D2:D4000 Rank Column is E2:E4000 Gender Column is G2:G4000 Thanks! Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct and Countif | Excel Discussion (Misc queries) | |||
COUNTIF or SUMPRODUCT? | Excel Worksheet Functions | |||
SUMPRODUCT/COUNTIF | Excel Discussion (Misc queries) | |||
sumproduct vs. countif | Excel Discussion (Misc queries) | |||
SUMPRODUCT & COUNTIF | Excel Worksheet Functions |