sumif

[TiaXL]

Hi All,

Appreciate some help if possible.

Is it possible to do a sumif on a <>value ? I have a bit list of order amounts and I want to add them up but I want to exclude a certain product name

 

[TiaXL]

Try

=SUMPRODUCT(--(S1:S100=W4),--(A1:A100<>U9),M1:M100)

gives a #NUM error

Could you also please explain how the SUMPRODUCT function works as its not something I have used before and see it thrown aroud a lot on this board.

 

[VoG]

Can you post your actual formula. You cannot use whole column references like A:A.

Info on SUMPRODUCT http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[TiaXL]

Can you post your actual formula. You cannot use whole column references like A:A.

Info on SUMPRODUCT http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I use whole column reference as the data set will grow daily.

my actual formula is =SUMPRODUCT(--(S:S=W4),--(A:A<>$U$9),M:M)

 

[VoG]

That won't work. Try

=SUMPRODUCT(--(S1:S10000=W4),--(A1:A10000<>$U$9),M1:M10000)

 

[TiaXL]

Yea figures that out and now it works thanks, I just used a1:a65500 instead of a:a :D