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

Try this...

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

--
Rick (MVP - Excel)


"Eric" wrote in message ...
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

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


On Dec 4, 12:59*am, Eric wrote:
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

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

--
Gary''s Student - gsnu200817

Thanks. That worked perfectly.


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200817

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 ?

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200817

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

will get it for you.

"Eric" wrote:

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