SUMIF with 3 criteria vs 2 criteria

J

jane

this works:
{=SUM(IF($H$9:$H$116=4,IF(LEFT($J$9:$J$116)="y",$I$9:$I$116))))}

but this does not
{=SUM(IF($H$9:$H$116=4,IF($H$9:$H$116=1,IF(LEFT($J$9:$J$116)="y",$I$9:$I$116))))}
need to know if col H contains a 1 or a 4 and J starts with a y - my result
is 0 but should be 2130

suggestions??? thanks in advance!, jane
 
R

Ron Coderre

Try this:

=SUMPRODUCT(($H$9:$H$116={1,4})*(LEFT($J$9:$J$116,1)="Y")*$I$9:$I$116)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
T

Teethless mama

Try like this:

{=SUM(IF($H$9:$H$116={1,4},IF(LEFT($J$9:$J$116)="y",$I$9:$I$116)))}
 
J

jane

it worked!
thank you!

Ron Coderre said:
Try this:

=SUMPRODUCT(($H$9:$H$116={1,4})*(LEFT($J$9:$J$116,1)="Y")*$I$9:$I$116)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Click to expand...
 
Top