add two criteria that should not be met to sumproduct
 

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

--
Diddy

add two criteria that should not be met to sumproduct
 

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

"Diddy" wrote:

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

--
Diddy

add two criteria that should not be met to sumproduct
 

Sorry Mike,

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

THANK YOU and Pete UK :-)
--
Diddy


"Mike H" wrote:

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

"Diddy" wrote:

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

--
Diddy

add two criteria that should not be met to sumproduct
 

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


"Mike H" wrote:

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

"Diddy" wrote:

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

--
Diddy