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?

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?

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?

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?

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?

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 "$"

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 "$"