Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct query
I'm using the below formula, to sum data from a specific range that meets my
specific criteria. I think there must be a mistake in how it is written, but i can't figure it out. =SUMPRODUCT(--('\\office1\shared\[Test1.xls]Sheet1'!E$11:E$20={11;12;13;15;17;18;19;20;32;33;3 4;52;69;82;83;85;86;87})) much obliged for any assistance on this. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct query
You are counting the number of matches with this. Remove the -- to sum the
matches. "shakey1181" wrote: I'm using the below formula, to sum data from a specific range that meets my specific criteria. I think there must be a mistake in how it is written, but i can't figure it out. =SUMPRODUCT(--('\\office1\shared\[Test1.xls]Sheet1'!E$11:E$20={11;12;13;15;17;18;19;20;32;33;3 4;52;69;82;83;85;86;87})) much obliged for any assistance on this. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct query
sorry, i meant count... either way, it still seems to be returning an N/A
error. Is the rest written correctly? "Barb Reinhardt" wrote: You are counting the number of matches with this. Remove the -- to sum the matches. "shakey1181" wrote: I'm using the below formula, to sum data from a specific range that meets my specific criteria. I think there must be a mistake in how it is written, but i can't figure it out. =SUMPRODUCT(--('\\office1\shared\[Test1.xls]Sheet1'!E$11:E$20={11;12;13;15;17;18;19;20;32;33;3 4;52;69;82;83;85;86;87})) much obliged for any assistance on this. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct query
Hi
Basically you are wanting a count of any cells in the range E11:E120 which contain any of the numbers in your array. It may be that your separator is different from that shown. In the US and the UK the separator between each item in the array would be a comma ={11,12,13,15,17......} Depending on which country version of Excel you are using, you may need to change the separator. -- Regards Roger Govier "shakey1181" wrote in message ... sorry, i meant count... either way, it still seems to be returning an N/A error. Is the rest written correctly? "Barb Reinhardt" wrote: You are counting the number of matches with this. Remove the -- to sum the matches. "shakey1181" wrote: I'm using the below formula, to sum data from a specific range that meets my specific criteria. I think there must be a mistake in how it is written, but i can't figure it out. =SUMPRODUCT(--('\\office1\shared\[Test1.xls]Sheet1'!E$11:E$20={11;12;13;15;17;18;19;20;32;33;3 4;52;69;82;83;85;86;87})) much obliged for any assistance on this. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct query
I've changed the seperator to commas, and it now returns a '0' value, which
is incorrect... "Roger Govier" wrote: Hi Basically you are wanting a count of any cells in the range E11:E120 which contain any of the numbers in your array. It may be that your separator is different from that shown. In the US and the UK the separator between each item in the array would be a comma ={11,12,13,15,17......} Depending on which country version of Excel you are using, you may need to change the separator. -- Regards Roger Govier "shakey1181" wrote in message ... sorry, i meant count... either way, it still seems to be returning an N/A error. Is the rest written correctly? "Barb Reinhardt" wrote: You are counting the number of matches with this. Remove the -- to sum the matches. "shakey1181" wrote: I'm using the below formula, to sum data from a specific range that meets my specific criteria. I think there must be a mistake in how it is written, but i can't figure it out. =SUMPRODUCT(--('\\office1\shared\[Test1.xls]Sheet1'!E$11:E$20={11;12;13;15;17;18;19;20;32;33;3 4;52;69;82;83;85;86;87})) much obliged for any assistance on this. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct query
Hi
Are the values in column E, Numeric or Text? Try entering in a spare column =ISNUMBER(E11) and copy down. Do they all return True? -- Regards Roger Govier "shakey1181" wrote in message ... I've changed the seperator to commas, and it now returns a '0' value, which is incorrect... "Roger Govier" wrote: Hi Basically you are wanting a count of any cells in the range E11:E120 which contain any of the numbers in your array. It may be that your separator is different from that shown. In the US and the UK the separator between each item in the array would be a comma ={11,12,13,15,17......} Depending on which country version of Excel you are using, you may need to change the separator. -- Regards Roger Govier "shakey1181" wrote in message ... sorry, i meant count... either way, it still seems to be returning an N/A error. Is the rest written correctly? "Barb Reinhardt" wrote: You are counting the number of matches with this. Remove the -- to sum the matches. "shakey1181" wrote: I'm using the below formula, to sum data from a specific range that meets my specific criteria. I think there must be a mistake in how it is written, but i can't figure it out. =SUMPRODUCT(--('\\office1\shared\[Test1.xls]Sheet1'!E$11:E$20={11;12;13;15;17;18;19;20;32;33;3 4;52;69;82;83;85;86;87})) much obliged for any assistance on this. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct query
Yes, I have checked that they are all numeric values. Should the character
before the array be '=' or something else? When I change it to a comma, it errors. "Roger Govier" wrote: Hi Are the values in column E, Numeric or Text? Try entering in a spare column =ISNUMBER(E11) and copy down. Do they all return True? -- Regards Roger Govier "shakey1181" wrote in message ... I've changed the seperator to commas, and it now returns a '0' value, which is incorrect... "Roger Govier" wrote: Hi Basically you are wanting a count of any cells in the range E11:E120 which contain any of the numbers in your array. It may be that your separator is different from that shown. In the US and the UK the separator between each item in the array would be a comma ={11,12,13,15,17......} Depending on which country version of Excel you are using, you may need to change the separator. -- Regards Roger Govier "shakey1181" wrote in message ... sorry, i meant count... either way, it still seems to be returning an N/A error. Is the rest written correctly? "Barb Reinhardt" wrote: You are counting the number of matches with this. Remove the -- to sum the matches. "shakey1181" wrote: I'm using the below formula, to sum data from a specific range that meets my specific criteria. I think there must be a mistake in how it is written, but i can't figure it out. =SUMPRODUCT(--('\\office1\shared\[Test1.xls]Sheet1'!E$11:E$20={11;12;13;15;17;18;19;20;32;33;3 4;52;69;82;83;85;86;87})) much obliged for any assistance on this. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct query
The formula should be
=SUMPRODUCT(--('\\office1\shared\[Test1.xls]Sheet1'!E$11:E$20={11,12,13,15,17,18,19,20,32,33,3 4,52,69,82,83,85,86,87})) -- Regards Roger Govier "shakey1181" wrote in message ... Yes, I have checked that they are all numeric values. Should the character before the array be '=' or something else? When I change it to a comma, it errors. "Roger Govier" wrote: Hi Are the values in column E, Numeric or Text? Try entering in a spare column =ISNUMBER(E11) and copy down. Do they all return True? -- Regards Roger Govier "shakey1181" wrote in message ... I've changed the seperator to commas, and it now returns a '0' value, which is incorrect... "Roger Govier" wrote: Hi Basically you are wanting a count of any cells in the range E11:E120 which contain any of the numbers in your array. It may be that your separator is different from that shown. In the US and the UK the separator between each item in the array would be a comma ={11,12,13,15,17......} Depending on which country version of Excel you are using, you may need to change the separator. -- Regards Roger Govier "shakey1181" wrote in message ... sorry, i meant count... either way, it still seems to be returning an N/A error. Is the rest written correctly? "Barb Reinhardt" wrote: You are counting the number of matches with this. Remove the -- to sum the matches. "shakey1181" wrote: I'm using the below formula, to sum data from a specific range that meets my specific criteria. I think there must be a mistake in how it is written, but i can't figure it out. =SUMPRODUCT(--('\\office1\shared\[Test1.xls]Sheet1'!E$11:E$20={11;12;13;15;17;18;19;20;32;33;3 4;52;69;82;83;85;86;87})) much obliged for any assistance on this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT IF query | Excel Discussion (Misc queries) | |||
SUMPRODUCT/HLOOKUP Query | Excel Discussion (Misc queries) | |||
SUMPRODUCT Query | Excel Discussion (Misc queries) | |||
Sumproduct Query | Excel Discussion (Misc queries) | |||
I think its a sumproduct query? | Excel Discussion (Misc queries) |