# add two criteria that should not be met to sumproduct

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
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

M

#### Mike H

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

D

#### Diddy

Sorry Mike,

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

THANK YOU and Pete UK

D

#### Diddy

I'll go back anf tick answered on original post as well