------ Register to get rid of these "In Post" ads! ------


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

------ Register to get rid of these "In Post" ads! ------


"Diddy" wrote:
> 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" wrote:
> 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 number
> =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

------ Register to get rid of these "In Post" ads! ------


Thank you Joe User :-)

"Joe User" wrote:

> "Diddy" wrote:
> > 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" wrote:
> > 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 number
> > =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

------ Register to get rid of these "In Post" ads! ------


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

--
Biff
Microsoft Excel MVP


"Diddy" <Diddy@discussions.microsoft.com> wrote in message
news:421B6D30-18DC-49B5-8A8B-051ACF03B6BD@microsoft.com...
> 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 number
> =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

------ Register to get rid of these "In Post" ads! ------


=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))
--
David Biddulph


"Diddy" <Diddy@discussions.microsoft.com> wrote in message
news:421B6D30-18DC-49B5-8A8B-051ACF03B6BD@microsoft.com...
> 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 number
> =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

------ Register to get rid of these "In Post" ads! ------


--((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")

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:kYCdnZkYDMgTChDWnZ2dnUVZ8kmdnZ2d@bt.com...
> =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))
> --
> David Biddulph
>
>
> "Diddy" <Diddy@discussions.microsoft.com> wrote in message
> news:421B6D30-18DC-49B5-8A8B-051ACF03B6BD@microsoft.com...
>> 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 number
>> =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
>
>

------ Register to get rid of these "In Post" ads! ------


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


"T. Valko" <biffinpitt@comcast.net> wrote in message
news:OgyWW5luKHA.1796@TK2MSFTNGP02.phx.gbl...
> --((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")
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "David Biddulph" <groups [at] biddulph.org.uk> wrote in message
> news:kYCdnZkYDMgTChDWnZ2dnUVZ8kmdnZ2d@bt.com...
>> =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))
>> --
>> David Biddulph
>>
>>
>> "Diddy" <Diddy@discussions.microsoft.com> wrote in message
>> news:421B6D30-18DC-49B5-8A8B-051ACF03B6BD@microsoft.com...
>>> 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 number
>>> =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
>>
>>
>
>