add two criteria that should not be met to sumproduct

  • Thread starter Thread starter Diddy
  • Start date Start date
D

Diddy

Hi everyone,

I posted this a little while ago and Pete UK answered but I got moved on to
another piece of work and I think my post had fallen off the screen by the
time I answered to say that Pete's suggestion hadn't worked- sorry about that
Pete UK. I'm now back to this piece of work and if anyone can help that would
be great!

I'm using the following
=SUMPRODUCT(--(Data!$C$2:$C$10000=$A6),--(Data!$S$2:$S$10000=1),--(Data!$K$2:$K$10000<>"N2"))

Which works beautifully but I also need to say that if k2:k10000 does not
equal N1 it shouldn't be counted as well.

Pete UK suggested

=SUMPRODUCT(--(Data!$C$2:$C$10000=$A6),--(Data!$S$2:$S$10000=1) ,--
(Data!$K$2:$K$10000<>"N2"),--(Data!$K$­2:$K$10000<>"N1"))

I got some strange results back with this one, so if Pete UK or anyone out
there can help I'd be so happy :-)

Grateful for any help
 
Hi,

What are the strange results you got? The formula you got from Pete UK looks
fine to me provided you are checking for the string "N1" in column K if you
want to refer to the range N1 then drop the quotes

=SUMPRODUCT(--(Data!$C$2:$C$10000=$A6),--(Data!$S$2:$S$10000=1),--(Data!$K$2:$K$10000<>N2),--(Data!$K$2:$K$10000<>N1))

Mike
 
Sorry Mike,

It does all it's supposed to do. Must have left brain at home - Again!

THANK YOU and Pete UK :-)
 
Back
Top