sumif or sum production - 3 or more criteria

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??
 
B

Bob Umlas

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
 
T

Teethless mama

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))
 
J

James

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top