SUMIF with 3 criteria vs 2 criteria

[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

 

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

 

[Tyro]

Excel 2007 has SUMIFS. You did not state your version.

Tyro

 

[Teethless mama]

Try like this:

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

 

[jane]

it worked!
thank you!

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)

 

[jane]

2003 version

Excel 2007 has SUMIFS. You did not state your version.

Tyro

 

[jane]

this also worked perfectly
thank you!

Try like this:

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