Remember Me?

#1
May 18th 07, 10:33 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 27
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
May 18th 07, 11:32 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,355
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
May 18th 07, 11:45 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 27
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
May 18th 07, 01:24 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,886
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
May 18th 07, 02:00 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 27
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
May 18th 07, 02:20 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,886
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
May 18th 07, 02:34 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 27
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
May 18th 07, 02:49 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,886
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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post sdg8481 Excel Discussion (Misc queries) 1 March 6th 07 05:25 PM luvthavodka Excel Discussion (Misc queries) 6 July 15th 06 06:58 PM penri0_0 Excel Discussion (Misc queries) 7 June 7th 06 12:22 PM shakey1181 Excel Discussion (Misc queries) 6 June 7th 06 11:56 AM Scoosh Excel Discussion (Misc queries) 0 September 8th 05 12:45 AM

All times are GMT +1. The time now is 11:03 PM.