Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with 2 ranges & 2 criteria
Hi, I have been trying to figure out a way to use the sumif with 2 criteria &
2 ranges...here is what it looks like Name Job Name Class Amount Lee Bolding Const Lot 27 Labor 1000 Tom Moran lot 25 Labor 5000 Seneca Hardwood lot 27 materials 2000 I want a formula that gives me the total amount of all job names containing "lot 27" in which contain the class "labor". I have been able to get the total amount of just lot 27, but not 2 different criteria. Can you help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with 2 ranges & 2 criteria
=SUMPRODUCT(--(Job_Name="Lot 27"),--(class="Labor"),Amount)
"cgibby" wrote: Hi, I have been trying to figure out a way to use the sumif with 2 criteria & 2 ranges...here is what it looks like Name Job Name Class Amount Lee Bolding Const Lot 27 Labor 1000 Tom Moran lot 25 Labor 5000 Seneca Hardwood lot 27 materials 2000 I want a formula that gives me the total amount of all job names containing "lot 27" in which contain the class "labor". I have been able to get the total amount of just lot 27, but not 2 different criteria. Can you help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with 2 ranges & 2 criteria
I don't know why, but it doesn't work...it just gave me "#Name?" - maybe it's
because i don't understand the sumproduct function and how it works...is there no way to do it with the sumif function? "Teethless mama" wrote: =SUMPRODUCT(--(Job_Name="Lot 27"),--(class="Labor"),Amount) "cgibby" wrote: Hi, I have been trying to figure out a way to use the sumif with 2 criteria & 2 ranges...here is what it looks like Name Job Name Class Amount Lee Bolding Const Lot 27 Labor 1000 Tom Moran lot 25 Labor 5000 Seneca Hardwood lot 27 materials 2000 I want a formula that gives me the total amount of all job names containing "lot 27" in which contain the class "labor". I have been able to get the total amount of just lot 27, but not 2 different criteria. Can you help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with 2 ranges & 2 criteria
It's probably because you don't have your options set to use row and
column labels. Try =SUMPRODUCT(--(B2:B4="Lot 27"),--(C2:C6="Labor"),D2:D6) In article , cgibby wrote: I don't know why, but it doesn't work...it just gave me "#Name?" - maybe it's because i don't understand the sumproduct function and how it works...is there no way to do it with the sumif function? "Teethless mama" wrote: =SUMPRODUCT(--(Job_Name="Lot 27"),--(class="Labor"),Amount) "cgibby" wrote: Hi, I have been trying to figure out a way to use the sumif with 2 criteria & 2 ranges...here is what it looks like Name Job Name Class Amount Lee Bolding Const Lot 27 Labor 1000 Tom Moran lot 25 Labor 5000 Seneca Hardwood lot 27 materials 2000 I want a formula that gives me the total amount of all job names containing "lot 27" in which contain the class "labor". I have been able to get the total amount of just lot 27, but not 2 different criteria. Can you help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with 2 ranges & 2 criteria
Ok I got that to work, thanks you!
i have another question though. I tried using (--($B:$B="Lot 27"),(--($C:$C="Labor"),$D:$D) to add all of the columns, but it didn't work. is there a reason why i can't do the "$" "JE McGimpsey" wrote: It's probably because you don't have your options set to use row and column labels. Try =SUMPRODUCT(--(B2:B4="Lot 27"),--(C2:C6="Labor"),D2:D6) In article , cgibby wrote: I don't know why, but it doesn't work...it just gave me "#Name?" - maybe it's because i don't understand the sumproduct function and how it works...is there no way to do it with the sumif function? "Teethless mama" wrote: =SUMPRODUCT(--(Job_Name="Lot 27"),--(class="Labor"),Amount) "cgibby" wrote: Hi, I have been trying to figure out a way to use the sumif with 2 criteria & 2 ranges...here is what it looks like Name Job Name Class Amount Lee Bolding Const Lot 27 Labor 1000 Tom Moran lot 25 Labor 5000 Seneca Hardwood lot 27 materials 2000 I want a formula that gives me the total amount of all job names containing "lot 27" in which contain the class "labor". I have been able to get the total amount of just lot 27, but not 2 different criteria. Can you help? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with 2 ranges & 2 criteria
Prior to XL07, array formulae (which SUMPRODUCTs are, even though they
don't need to be entered with CTRL-SHIFT-ENTER) don't accept entire columns as arguments. You could use something like: --($B$1:$B65535="Lot 27") In article , cgibby wrote: Ok I got that to work, thanks you! i have another question though. I tried using (--($B:$B="Lot 27"),(--($C:$C="Labor"),$D:$D) to add all of the columns, but it didn't work. is there a reason why i can't do the "$" |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with 2 ranges & 2 criteria
ok thanks so much for your help!
"JE McGimpsey" wrote: Prior to XL07, array formulae (which SUMPRODUCTs are, even though they don't need to be entered with CTRL-SHIFT-ENTER) don't accept entire columns as arguments. You could use something like: --($B$1:$B65535="Lot 27") In article , cgibby wrote: Ok I got that to work, thanks you! i have another question though. I tried using (--($B:$B="Lot 27"),(--($C:$C="Labor"),$D:$D) to add all of the columns, but it didn't work. is there a reason why i can't do the "$" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF ranges | Excel Worksheet Functions | |||
SUMIF using two ranges | Excel Discussion (Misc queries) | |||
Calculate a SUMIF if criteria is between 2 date ranges | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions |