|
Sumproduct neither nor
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 |
Sumproduct neither nor
"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 |
Sumproduct neither nor
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 |
Sumproduct neither nor
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" wrote in message ... 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 |
Sumproduct neither nor
=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" wrote in message ... 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 |
Sumproduct neither nor
--((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 ... =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" wrote in message ... 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 |
Sumproduct neither nor
True. I copied that part without reading it. Well spotted!
-- David Biddulph "T. Valko" wrote in message ... --((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 ... =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" wrote in message ... 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 |
| All times are GMT +1. The time now is 12:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com