SUMIF with 3 criteria vs 2 criteria

  • Thread starter Thread starter jane
  • Start date Start date
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
 
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)
 
Try like this:

{=SUM(IF($H$9:$H$116={1,4},IF(LEFT($J$9:$J$116)="y",$I$9:$I$116)))}
 
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...
 
Back
Top