| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct help. | Excel Worksheet Functions | |||
| sumproduct | Excel Discussion (Misc queries) | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Linear Mode
