Use of SUMPRODUCT with Criterea

[Kym]

I am looking for a method to use sumproduct on multiple
cells accross a series of columns, BUT I only want to do
the calculation if the column meets a criterea.

For instance, in the following example, I want to
sumproduct that multiplies row 2 and 3, provided the
criterea in row 1 meets my requirement. If I set the
required criterea as "S", then I want the result to be
(B2*B3) + (D2*D3). Similarly, if my criterea is "P",
then the result should be (C2*C3)+(E2*E3)


A B C D E
1 S P S P
2 Tonnes 1000 50 1000 50
3 Grade 10 7 10 7

Any help would be appreciated

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--(B1:E1="S"),B2:E2,B3:e3)

 

[Kym]

Thanks Frank

Works a treat via your method. Also since I sent the SOS
out, I've found another method as follows.

=SUMPRODUCT((B5:I5="S")*B6:I6,B7:I7)

This also works a treat. Multiplies row 6 times row 7
depending on finding an "S" in row 5. Also in my
application, I'm using a cell reference for the "S"
criterea.

Appreciate your help out here in the middle of
Australia. Where I am is pretty central to ALL of
Australia's beaches. About the same distance to them all.

Kym