sumif or sum production - 3 or more criteria
 

Excerpt from my s/s
A C I N
pipe steel no grade
size grade jonts result

4 J-55 100 white
4 J-55 50 yellow
5 N-80 50 blue
4 N-80 75 green

I want to sum from column I based on criteria in cols. A,C & N. I have
tried this:
=SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a 0
result.
I tried the sumproduct method:
=SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9))
It yielded 375 which is incorrect. With more than 2 criteria since it is an
array it counts the TRUE's for a criteria and multiplies by the corresponding
value in the I column instead of summing in I if all 3 criteria are met.
The correct answer for my criteria set is 50.
Suggestions??

sumif or sum production - 3 or more criteria
 

Your SUM(IF(... has incorrect syntax -- it still needs to follow the
condition, value if true, value if false.
You're close with the SUMPRODUCT - just missing commas:
=SUMPRODUCT(--(A6:A9=4),--(C6:C9="j-55"),--(O6:O9="yellow"),(I6:I9))
the above should work

"James" wrote in message
...
Excerpt from my s/s
A C I N
pipe steel no grade
size grade jonts result

4 J-55 100 white
4 J-55 50 yellow
5 N-80 50 blue
4 N-80 75 green

I want to sum from column I based on criteria in cols. A,C & N. I have
tried this:
=SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a
0
result.
I tried the sumproduct method:
=SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9))
It yielded 375 which is incorrect. With more than 2 criteria since it is
an
array it counts the TRUE's for a criteria and multiplies by the
corresponding
value in the I column instead of summing in I if all 3 criteria are met.
The correct answer for my criteria set is 50.
Suggestions??

sumif or sum production - 3 or more criteria
 

James,

Try this

=SUMPRODUCT((A6:A9=4)*(C6:C9="j-55")*(N6:N9="yellow")*(I6:I9))

Mike

"James" wrote:

Excerpt from my s/s
A C I N
pipe steel no grade
size grade jonts result

4 J-55 100 white
4 J-55 50 yellow
5 N-80 50 blue
4 N-80 75 green

I want to sum from column I based on criteria in cols. A,C & N. I have
tried this:
=SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a 0
result.
I tried the sumproduct method:
=SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9))
It yielded 375 which is incorrect. With more than 2 criteria since it is an
array it counts the TRUE's for a criteria and multiplies by the corresponding
value in the I column instead of summing in I if all 3 criteria are met.
The correct answer for my criteria set is 50.
Suggestions??

sumif or sum production - 3 or more criteria
 

You have a right syntax on your first formula. All you have to commit with
ctrl+shift+enter, not just enter

=SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9))


The second formula should be like this:
=SUMPRODUCT(--(A6:A9=4),--(C6:C9="j-55"),--(N6:N9="yellow"),(I6:I9))


"James" wrote:

Excerpt from my s/s
A C I N
pipe steel no grade
size grade jonts result

4 J-55 100 white
4 J-55 50 yellow
5 N-80 50 blue
4 N-80 75 green

I want to sum from column I based on criteria in cols. A,C & N. I have
tried this:
=SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a 0
result.
I tried the sumproduct method:
=SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9))
It yielded 375 which is incorrect. With more than 2 criteria since it is an
array it counts the TRUE's for a criteria and multiplies by the corresponding
value in the I column instead of summing in I if all 3 criteria are met.
The correct answer for my criteria set is 50.
Suggestions??

sumif or sum production - 3 or more criteria
 

thanks

Both suggetions (MH & TM) still yield a 0 result. The values in cells in
column A are from a picklist with fixed decimal places. Could this be
causing the problem? Seems I was reading something about not having any
formatting in your data.

"Teethless mama" wrote:

You have a right syntax on your first formula. All you have to commit with
ctrl+shift+enter, not just enter

=SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9))


The second formula should be like this:
=SUMPRODUCT(--(A6:A9=4),--(C6:C9="j-55"),--(N6:N9="yellow"),(I6:I9))


"James" wrote:

Excerpt from my s/s
A C I N
pipe steel no grade
size grade jonts result

4 J-55 100 white
4 J-55 50 yellow
5 N-80 50 blue
4 N-80 75 green

I want to sum from column I based on criteria in cols. A,C & N. I have
tried this:
=SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a 0
result.
I tried the sumproduct method:
=SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9))
It yielded 375 which is incorrect. With more than 2 criteria since it is an
array it counts the TRUE's for a criteria and multiplies by the corresponding
value in the I column instead of summing in I if all 3 criteria are met.
The correct answer for my criteria set is 50.
Suggestions??

sumif or sum production - 3 or more criteria
 

check your data to make sure no leading and/or trailing spaces.


"James" wrote:

thanks

Both suggetions (MH & TM) still yield a 0 result. The values in cells in
column A are from a picklist with fixed decimal places. Could this be
causing the problem? Seems I was reading something about not having any
formatting in your data.

"Teethless mama" wrote:

You have a right syntax on your first formula. All you have to commit with
ctrl+shift+enter, not just enter

=SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9))


The second formula should be like this:
=SUMPRODUCT(--(A6:A9=4),--(C6:C9="j-55"),--(N6:N9="yellow"),(I6:I9))


"James" wrote:

Excerpt from my s/s
A C I N
pipe steel no grade
size grade jonts result

4 J-55 100 white
4 J-55 50 yellow
5 N-80 50 blue
4 N-80 75 green

I want to sum from column I based on criteria in cols. A,C & N. I have
tried this:
=SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a 0
result.
I tried the sumproduct method:
=SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9))
It yielded 375 which is incorrect. With more than 2 criteria since it is an
array it counts the TRUE's for a criteria and multiplies by the corresponding
value in the I column instead of summing in I if all 3 criteria are met.
The correct answer for my criteria set is 50.
Suggestions??