Sum Product Question

[Chad]

Hello all. I am having a problem witha sum product formula. I have Mulitple
Conditions and have to use the + sign to include all of them. Hereis an
example of my formula:

=Sumproduct((Range1=200601)*((Range2<>1000)+(Range2<>2534)+(Range2<>7486))*(Range3)*(Range4))

This formula returns a value but it is much higher than it should be. I
have read places about possbily using an array inside myformula to help take
care of this but I have not had any luck yet.

Any help you can provide would be greatly appreciated.

Thanks,
Chad

 

[Guest]

Hi

I would suggest using separate SUMPRODUCTs rather than using the + method:
=Sumproduct((Range1=200601)*(Range2<>1000)*(Range3)*(Range4))+Sumproduct((Range1=200601)*(Range2<>2534)*(Range3)*(Range4))+Sumproduct((Range1=200601)*(Range2<>7486)*(Range3)*(Range4))

Hope this helps.
Andy.

 

[Bob Phillips]

You probably want

=SUMPRODUCT(--(Range1=200601),--(Range2<>1000),--(Range2<>2534),--(Range2<>7
486),Range3,Range4)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Aladin Akyurek]

Looks like...

=SUMPRODUCT(--(Range1=200601),--ISNA(MATCH(Range2,{1000,2534,7486},0)),Range3,Range4)