J
James
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??
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??