Sumproduct neither nor

[Diddy]

Hi everyone,

I've been using
=SUMPRODUCT(--(Data!$C$3:$C$9875=$A5),--((Data!$K$3:$K$9875="N1")+(Data!$K$3:$K$9875="N2")),--((Data!$AC$3:$AC$9875="c")+(Data!$AC$3:$AC$9875="m")))
but now as part of the checking of the workbook I want a count of the
opposite where column K does not = N1 or N2.

I'm doing it the clunky way and using + every other value that K can hold
(numeric and alphanumeric) but there are a lot more of them than the N1, N2
so it would be much neater just to be able to say neither, nor

I've tried this but it returns an unexpected numbe
=SUMPRODUCT(--(Data!$C$3:$C$9875=$A5),--((Data!$K$3:$K$9875<>"N1")+(Data!$K$3:$K$9875<>"N2")),--((Data!$AC$3:$AC$9875="c")+(Data!$AC$3:$AC$9875="m")))

Where am I going wrong?

Many thanks
Diddy

 

[Joe User]

I've been using
=SUMPRODUCT(--(Data!$C$3:$C$9875=$A5),
--((Data!$K$3:$K$9875="N1")+
(Data!$K$3:$K$9875="N2")),
--((Data!$AC$3:$AC$9875="c")+(Data!$AC$3:$AC$9875="m")))
but now as part of the checking of the workbook I want a count of the
opposite where column K does not = N1 or N2.

Try:

=SUMPRODUCT(--(Data!$C$3:$C$9875=$A5),
--((Data!$K$3:$K$9875="N1")+
(Data!$K$3:$K$9875="N2")=0),
((Data!$AC$3:$AC$9875="c")+(Data!$AC$3:$AC$9875="m")))

Note that I eliminated some superfluous double-negation. In fact, you can
remove them all with the following:

=SUMPRODUCT((Data!$C$3:$C$9875=$A5)*
((Data!$K$3:$K$9875="N1")+
(Data!$K$3:$K$9875="N2")=0)*
((Data!$AC$3:$AC$9875={"c","m"})))

Note the simplication in the last term. I threw that in for demonstration
purposes only. Similarly, you could have written
Data!$K$3:$K$9875={"N1","N2"} in the first place, i.e. when you want to count
when column K is "N1" or "N2". However, that does make it difficult to
modify the formula to count when column K is neither "N1" nor "N2".


----- original message -----

 

[Diddy]

Thank you Joe User

Try:

=SUMPRODUCT(--(Data!$C$3:$C$9875=$A5),
--((Data!$K$3:$K$9875="N1")+
(Data!$K$3:$K$9875="N2")=0),
((Data!$AC$3:$AC$9875="c")+(Data!$AC$3:$AC$9875="m")))

Note that I eliminated some superfluous double-negation. In fact, you can
remove them all with the following:

=SUMPRODUCT((Data!$C$3:$C$9875=$A5)*
((Data!$K$3:$K$9875="N1")+
(Data!$K$3:$K$9875="N2")=0)*
((Data!$AC$3:$AC$9875={"c","m"})))

Note the simplication in the last term. I threw that in for demonstration
purposes only. Similarly, you could have written
Data!$K$3:$K$9875={"N1","N2"} in the first place, i.e. when you want to count
when column K is "N1" or "N2". However, that does make it difficult to
modify the formula to count when column K is neither "N1" nor "N2".


----- original message -----

 

[T. Valko]

Try this...

=SUMPRODUCT(--(Data!$C$3:$C$9875=$A5),--(ISNA(MATCH(Data!$K$3:$K$9875,{"N1","N2"},0))),--(ISNUMBER(MATCH(Data!$AC$3:$AC$9875,{"c","m"},0))))

 

[David Biddulph]

=SUMPRODUCT(--(Data!$C$3:$C$9875=$A5),--(Data!$K$3:$K$9875<>"N1"),--(Data!$K$3:$K$9875<>"N2"),--((Data!$AC$3:$AC$9875="c")+(Data!$AC$3:$AC$9875="m")))

Remember your Boolean Algebra:
NOT(OR(A,B)) = AND(NOT(A),NOT(B))

 

[T. Valko]

--((Data!$AC$3:$AC$9875="c")+(Data!$AC$3:$AC$9875="m"))

No need for the double unary in this application.

(Data!$AC$3:$AC$9875="c")+(Data!$AC$3:$AC$9875="m")

 

[David Biddulph]

True. I copied that part without reading it. Well spotted!