Sumproduct neither nor

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

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
 
Diddy said:
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.
Click to expand...

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 -----
 
Thank you Joe User :-)

Joe User said:
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 -----
Click to expand...
 
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))))
 
--((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")
 
Back
Top