Sumproduct criteria

[Eric]

Is there a way to use Sumproduct for a range but exclude the calculation of
certain rows. In example below use =sumproduct(a2:a6*c2:e6) , but somehow
exclude where "criteria" is "y?"


A B C D E
1 Criteria Multiplier Jan Feb Mar
2 x 0.5 120 105 90
3 x 0.4 90 85 120
4 y 0.7 105 120 85
5 y 0.4 120 70 105
6 x 0.2 90 90 70

Thanks

 

[Rick Rothstein]

Try this...

=SUMPRODUCT(((A2:A6<>"y")*B2:B6)*C2:E6)

 

[muddan madhu]

=SUMPRODUCT((A2:B6="x")*(B2:B6*C2:E6))

 

[Gary''s Student]

=SUMPRODUCT((B2:B6)*(C2:C6)*(A2:A6<>"y"))

 

[Sean Timmons]

=SUMPRODUCT(--(A2:A6<>"Y"),B2:B6,C2:C6,D26,E2:E6)

will get it for you.

 

[Eric]

Thanks. That worked perfectly.

 

[Harsh Bahal]

Hi Gary,

What if some cells are blanks in the value coloumn or there are no value and
I don't want a return answer of #div ?