| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a better way?
Hi everyone,
I'm using =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="c"))+SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="m")) So it counts if C = a5, K = 10, I = M and AC = either c or m Is there a better way to do it? -- Diddy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way?
Hi,
try this. Not tested SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--((Data!$AC$2:$AC$10000="c")+(Data!$AC$2:$AC$10000= "m")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Diddy" wrote in message ... Hi everyone, I'm using =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="c"))+SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="m")) So it counts if C = a5, K = 10, I = M and AC = either c or m Is there a better way to do it? -- Diddy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way?
Another alternative option:
=SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(ISNUMBER(MATCH(Data!$AC$2:$AC$10000,{"c";"m"},0)) )) Joy? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Diddy" wrote: I'm using =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="c"))+SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="m")) So it counts if C = a5, K = 10, I = M and AC = either c or m Is there a better way to do it? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way?
Thank you Ashish,
It needed another ) at the end. So I didn't need to to repeat the criteria for the other columns again. Interesting! Thanks -- Diddy "Ashish Mathur" wrote: Hi, try this. Not tested SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--((Data!$AC$2:$AC$10000="c")+(Data!$AC$2:$AC$10000= "m")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Diddy" wrote in message ... Hi everyone, I'm using =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="c"))+SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="m")) So it counts if C = a5, K = 10, I = M and AC = either c or m Is there a better way to do it? -- Diddy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way?
Hi Max,
Yes it works beautifully. I'm going to attempt to work out how it does it but it may be a long job. Food for thought! Thanks again -- Diddy "Max" wrote: Another alternative option: =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(ISNUMBER(MATCH(Data!$AC$2:$AC$10000,{"c";"m"},0)) )) Joy? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Diddy" wrote: I'm using =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="c"))+SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="m")) So it counts if C = a5, K = 10, I = M and AC = either c or m Is there a better way to do it? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way?
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Diddy" wrote in message ... Thank you Ashish, It needed another ) at the end. So I didn't need to to repeat the criteria for the other columns again. Interesting! Thanks -- Diddy "Ashish Mathur" wrote: Hi, try this. Not tested SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--((Data!$AC$2:$AC$10000="c")+(Data!$AC$2:$AC$10000= "m")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Diddy" wrote in message ... Hi everyone, I'm using =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="c"))+SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="m")) So it counts if C = a5, K = 10, I = M and AC = either c or m Is there a better way to do it? -- Diddy |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way?
Max,
I would say this is creative! -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Max" wrote: Another alternative option: =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(ISNUMBER(MATCH(Data!$AC$2:$AC$10000,{"c";"m"},0)) )) Joy? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Diddy" wrote: I'm using =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="c"))+SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="m")) So it counts if C = a5, K = 10, I = M and AC = either c or m Is there a better way to do it? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way?
--((Data!$AC$2:$AC$10000="c")+(Data!$AC$2:$AC$10000= "m"))
Since the range can contain only one or the other but not both at the same time, the result of (array1)+(array2) can only be the numbers 1 or 0 so there's no need to use the double unary on this particular array. The additon of the arrays will corece to numeric 1 or 0. -- Biff Microsoft Excel MVP "Ashish Mathur" wrote in message ... Hi, try this. Not tested SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--((Data!$AC$2:$AC$10000="c")+(Data!$AC$2:$AC$10000= "m")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Diddy" wrote in message ... Hi everyone, I'm using =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="c"))+SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="m")) So it counts if C = a5, K = 10, I = M and AC = either c or m Is there a better way to do it? -- Diddy |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way?
Welcome, Diddy
-- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Diddy" wrote in message ... Hi Max, Yes it works beautifully. I'm going to attempt to work out how it does it but it may be a long job. Food for thought! Thanks again |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way?
Thanks for the compliments, Francis.
-- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "francis" wrote in message ... Max, I would say this is creative! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way?
Thank you Biff,
I'm determined to understand SUMPRODUCT one day and each bit of info helps :-) -- Diddy "T. Valko" wrote: --((Data!$AC$2:$AC$10000="c")+(Data!$AC$2:$AC$10000= "m")) Since the range can contain only one or the other but not both at the same time, the result of (array1)+(array2) can only be the numbers 1 or 0 so there's no need to use the double unary on this particular array. The additon of the arrays will corece to numeric 1 or 0. -- Biff Microsoft Excel MVP "Ashish Mathur" wrote in message ... Hi, try this. Not tested SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--((Data!$AC$2:$AC$10000="c")+(Data!$AC$2:$AC$10000= "m")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Diddy" wrote in message ... Hi everyone, I'm using =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="c"))+SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="m")) So it counts if C = a5, K = 10, I = M and AC = either c or m Is there a better way to do it? -- Diddy |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way?
For a lot more about SUMPRODUCT:
http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "Diddy" wrote in message ... Thank you Biff, I'm determined to understand SUMPRODUCT one day and each bit of info helps :-) -- Diddy "T. Valko" wrote: --((Data!$AC$2:$AC$10000="c")+(Data!$AC$2:$AC$10000= "m")) Since the range can contain only one or the other but not both at the same time, the result of (array1)+(array2) can only be the numbers 1 or 0 so there's no need to use the double unary on this particular array. The additon of the arrays will corece to numeric 1 or 0. -- Biff Microsoft Excel MVP "Ashish Mathur" wrote in message ... Hi, try this. Not tested SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--((Data!$AC$2:$AC$10000="c")+(Data!$AC$2:$AC$10000= "m")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Diddy" wrote in message ... Hi everyone, I'm using =SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="c"))+SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="m")) So it counts if C = a5, K = 10, I = M and AC = either c or m Is there a better way to do it? -- Diddy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
