Nesting SUMPRODUCT within SUMIF

[giaudi]

Hi guys, I need your help.

I'm trying to tell excel to SUMPRODUCT 2 arrays only if the code in a colum is equal to the one specified, but nesting sumproduct within sumif doesn't seem to be working

eg

columns A B C

x 3 2
y 2 5
x 5 5

I write:

=SUMIF(A:A;"x";SUMPRODUCT(B:B;C:C))

which should give the below result:

3*2 + 5*5 = 31

Do you ave any alternative?

Thanks in advance

G

 

[Aladin Akyurek]

That would be just...

=SUMPRODUCT(--(A2:A1000="X"),B2:B1000,C2:C1000)

 

[Norie]

Why would you nest the 2 functions?

Just use SUMPRODUCT.

=SUMPRODUCT(--(A1:A3="x"), B1:B3, C1:C3)

 

[Richard Schollar]

Hi

Just use Sumproduct:

=SUMPRODUCT((A1:A3="x")+0,B1:B3,C1:C3)

Note that if you are using xl2003 or below, you can't use whole column refs (such as A:A or B:B) in the Sumproduct - you need to explicitly define your range (eg A1:A3).

 

[giaudi]

Cool guys. Right, I was complicating it

And if I wanted to multiply numbers in column B by a constant number, say 10?

 

[Richard Schollar]

Just as easy:

=SUMPRODUCT((A1:A3="x")+0,B1:B3*10,C1:C3)

 

[Aladin Akyurek]

Cool guys. Right, I was complicating it

And if I wanted to multiply numbers in column B by a constant number, say 10?

Underspecified...

Either (if column C is irrelevant)...

=SUMIF(A2:A1000,"X",B2:B1000)*10

Or...

=SUMPRODUCT(--(A2:A1000="X"),B2:B1000*10,C2:C1000)

 

[pgc01]

Hi

Also

=10*SUMPRODUCT(--(A2:A1000="X"),B2:B1000,C2:C1000) <!-- / message --><!-- sig -->

 

[giaudi]

Thanks a lot guys!