#1   Report Post  
Jimbo
 
Posts: n/a
Default SUMPRODUCT

Greetings,
Im looking for an explanation for something happening in Excel that I dont
understand. In a worksheet cell I have a formula
€œ=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$AN$2:$AN$5000))€
which works fine. Regardless of how I try to do it, I cant copy it to any
cell on any other worksheet without an error €œ#VALUE!€ rising from the
€œ--([Data.xls]A!$AN$2:$AN$5000))€ array. I dont understand why I get an
error when I know the formula works fine.
Appreciate whatever help you can give me!
Jim

  #2   Report Post  
Alok
 
Posts: n/a
Default

I tried it in XL2K but could not reproduce the error.

Alok Joshi

"Jimbo" wrote:

Greetings,
Im looking for an explanation for something happening in Excel that I dont
understand. In a worksheet cell I have a formula
€œ=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$AN$2:$AN$5000))€
which works fine. Regardless of how I try to do it, I cant copy it to any
cell on any other worksheet without an error €œ#VALUE!€ rising from the
€œ--([Data.xls]A!$AN$2:$AN$5000))€ array. I dont understand why I get an
error when I know the formula works fine.
Appreciate whatever help you can give me!
Jim

  #3   Report Post  
bj
 
Posts: n/a
Default

is it just the AN column array you are having problems with, or the full
equation?
try a
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000))
if you get a 1 try
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000))
if you get more that 1 try reducing the range until you do get 1

"Jimbo" wrote:

Greetings,
Im looking for an explanation for something happening in Excel that I dont
understand. In a worksheet cell I have a formula
€œ=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$AN$2:$AN$5000))€
which works fine. Regardless of how I try to do it, I cant copy it to any
cell on any other worksheet without an error €œ#VALUE!€ rising from the
€œ--([Data.xls]A!$AN$2:$AN$5000))€ array. I dont understand why I get an
error when I know the formula works fine.
Appreciate whatever help you can give me!
Jim

  #4   Report Post  
Jimbo
 
Posts: n/a
Default

Thanks bj,

What I get with both of these is"0".

Jim

"bj" wrote:

is it just the AN column array you are having problems with, or the full
equation?
try a
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000))
if you get a 1 try
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000))
if you get more that 1 try reducing the range until you do get 1

"Jimbo" wrote:

Greetings,
Im looking for an explanation for something happening in Excel that I dont
understand. In a worksheet cell I have a formula
€œ=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$AN$2:$AN$5000))€
which works fine. Regardless of how I try to do it, I cant copy it to any
cell on any other worksheet without an error €œ#VALUE!€ rising from the
€œ--([Data.xls]A!$AN$2:$AN$5000))€ array. I dont understand why I get an
error when I know the formula works fine.
Appreciate whatever help you can give me!
Jim

  #5   Report Post  
bj
 
Posts: n/a
Default

what do you get with just
=sumproduct(--([Data.xls]A!$AN$2:$AN$5000))
and
=sumproduct(--([Data.xls]A!$BN$2:$BN$5000))
and
=sumproduct((--([Data.xls]A!$AN$2:$AN$5000),--([Data.xls]A!$BN$2:$BN$5000))
and
=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$BN$2:$BN$5000))



assuming BN is blank
the reason I am asking is that the only ways I know to get an "#value with
sumproduct is to have a #Value error in one of the cells or when there are
unequal arrays.

"Jimbo" wrote:

Thanks bj,

What I get with both of these is"0".

Jim

"bj" wrote:

is it just the AN column array you are having problems with, or the full
equation?
try a
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000))
if you get a 1 try
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000))
if you get more that 1 try reducing the range until you do get 1

"Jimbo" wrote:

Greetings,
Im looking for an explanation for something happening in Excel that I dont
understand. In a worksheet cell I have a formula
€œ=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$AN$2:$AN$5000))€
which works fine. Regardless of how I try to do it, I cant copy it to any
cell on any other worksheet without an error €œ#VALUE!€ rising from the
€œ--([Data.xls]A!$AN$2:$AN$5000))€ array. I dont understand why I get an
error when I know the formula works fine.
Appreciate whatever help you can give me!
Jim



  #6   Report Post  
Jimbo
 
Posts: n/a
Default

I tried these as with the following results on the original sheet in my
workbook:
75
0
0
0
You are correct in that there is no data in BN. Next I copied all four of
these cells to another worksheet and in that sheet the results a
"#VALUE!"
0
"VALUE!"
0
At least it's consistent, but not as expected.
"bj" wrote:

what do you get with just
=sumproduct(--([Data.xls]A!$AN$2:$AN$5000))
and
=sumproduct(--([Data.xls]A!$BN$2:$BN$5000))
and
=sumproduct((--([Data.xls]A!$AN$2:$AN$5000),--([Data.xls]A!$BN$2:$BN$5000))
and
=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$BN$2:$BN$5000))



assuming BN is blank
the reason I am asking is that the only ways I know to get an "#value with
sumproduct is to have a #Value error in one of the cells or when there are
unequal arrays.

"Jimbo" wrote:

Thanks bj,

What I get with both of these is"0".

Jim

"bj" wrote:

is it just the AN column array you are having problems with, or the full
equation?
try a
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000))
if you get a 1 try
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000))
if you get more that 1 try reducing the range until you do get 1

"Jimbo" wrote:

Greetings,
Im looking for an explanation for something happening in Excel that I dont
understand. In a worksheet cell I have a formula
€œ=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$AN$2:$AN$5000))€
which works fine. Regardless of how I try to do it, I cant copy it to any
cell on any other worksheet without an error €œ#VALUE!€ rising from the
€œ--([Data.xls]A!$AN$2:$AN$5000))€ array. I dont understand why I get an
error when I know the formula works fine.
Appreciate whatever help you can give me!
Jim

  #7   Report Post  
bj
 
Posts: n/a
Default

this problem has succeeded is confusing me. (not really that hard to do)

Is the 75 the expected number for the sum of the AN column?

I have no clue why you would get a "Value" for the data copied to another
sheet.

"Jimbo" wrote:

I tried these as with the following results on the original sheet in my
workbook:
75
0
0
0
You are correct in that there is no data in BN. Next I copied all four of
these cells to another worksheet and in that sheet the results a
"#VALUE!"
0
"VALUE!"
0
At least it's consistent, but not as expected.
"bj" wrote:

what do you get with just
=sumproduct(--([Data.xls]A!$AN$2:$AN$5000))
and
=sumproduct(--([Data.xls]A!$BN$2:$BN$5000))
and
=sumproduct((--([Data.xls]A!$AN$2:$AN$5000),--([Data.xls]A!$BN$2:$BN$5000))
and
=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$BN$2:$BN$5000))



assuming BN is blank
the reason I am asking is that the only ways I know to get an "#value with
sumproduct is to have a #Value error in one of the cells or when there are
unequal arrays.

"Jimbo" wrote:

Thanks bj,

What I get with both of these is"0".

Jim

"bj" wrote:

is it just the AN column array you are having problems with, or the full
equation?
try a
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000))
if you get a 1 try
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000))
if you get more that 1 try reducing the range until you do get 1

"Jimbo" wrote:

Greetings,
Im looking for an explanation for something happening in Excel that I dont
understand. In a worksheet cell I have a formula
€œ=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$AN$2:$AN$5000))€
which works fine. Regardless of how I try to do it, I cant copy it to any
cell on any other worksheet without an error €œ#VALUE!€ rising from the
€œ--([Data.xls]A!$AN$2:$AN$5000))€ array. I dont understand why I get an
error when I know the formula works fine.
Appreciate whatever help you can give me!
Jim

  #8   Report Post  
Jimbo
 
Posts: n/a
Default

The confusion is the reason I initiated this inquiry - I've copied lots of
formulas to different sheets and workbooks for that matter, but have never
encountered a problem like this before. I'm in the same boat as you as what
I'm seeing makes no sense to me and I have no clue as to what is wrong. In
response to your question, I confirm that the correct sum for column AN is
75.

Perhaps my only recourse is to trash the whole workbook and start over, but
I was hoping I could avoid doing that.

"bj" wrote:

this problem has succeeded is confusing me. (not really that hard to do)

Is the 75 the expected number for the sum of the AN column?

I have no clue why you would get a "Value" for the data copied to another
sheet.

"Jimbo" wrote:

I tried these as with the following results on the original sheet in my
workbook:
75
0
0
0
You are correct in that there is no data in BN. Next I copied all four of
these cells to another worksheet and in that sheet the results a
"#VALUE!"
0
"VALUE!"
0
At least it's consistent, but not as expected.
"bj" wrote:

what do you get with just
=sumproduct(--([Data.xls]A!$AN$2:$AN$5000))
and
=sumproduct(--([Data.xls]A!$BN$2:$BN$5000))
and
=sumproduct((--([Data.xls]A!$AN$2:$AN$5000),--([Data.xls]A!$BN$2:$BN$5000))
and
=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$BN$2:$BN$5000))



assuming BN is blank
the reason I am asking is that the only ways I know to get an "#value with
sumproduct is to have a #Value error in one of the cells or when there are
unequal arrays.

"Jimbo" wrote:

Thanks bj,

What I get with both of these is"0".

Jim

"bj" wrote:

is it just the AN column array you are having problems with, or the full
equation?
try a
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000))
if you get a 1 try
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000))
if you get more that 1 try reducing the range until you do get 1

"Jimbo" wrote:

Greetings,
Im looking for an explanation for something happening in Excel that I dont
understand. In a worksheet cell I have a formula
€œ=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$AN$2:$AN$5000))€
which works fine. Regardless of how I try to do it, I cant copy it to any
cell on any other worksheet without an error €œ#VALUE!€ rising from the
€œ--([Data.xls]A!$AN$2:$AN$5000))€ array. I dont understand why I get an
error when I know the formula works fine.
Appreciate whatever help you can give me!
Jim

  #9   Report Post  
bj
 
Posts: n/a
Default

It does sound as though you may have a corrupted file. There may be work
arounds, but my experience has been when a file gets corrupted, it often
quickly gets worse.

"Jimbo" wrote:

The confusion is the reason I initiated this inquiry - I've copied lots of
formulas to different sheets and workbooks for that matter, but have never
encountered a problem like this before. I'm in the same boat as you as what
I'm seeing makes no sense to me and I have no clue as to what is wrong. In
response to your question, I confirm that the correct sum for column AN is
75.

Perhaps my only recourse is to trash the whole workbook and start over, but
I was hoping I could avoid doing that.

"bj" wrote:

this problem has succeeded is confusing me. (not really that hard to do)

Is the 75 the expected number for the sum of the AN column?

I have no clue why you would get a "Value" for the data copied to another
sheet.

"Jimbo" wrote:

I tried these as with the following results on the original sheet in my
workbook:
75
0
0
0
You are correct in that there is no data in BN. Next I copied all four of
these cells to another worksheet and in that sheet the results a
"#VALUE!"
0
"VALUE!"
0
At least it's consistent, but not as expected.
"bj" wrote:

what do you get with just
=sumproduct(--([Data.xls]A!$AN$2:$AN$5000))
and
=sumproduct(--([Data.xls]A!$BN$2:$BN$5000))
and
=sumproduct((--([Data.xls]A!$AN$2:$AN$5000),--([Data.xls]A!$BN$2:$BN$5000))
and
=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$BN$2:$BN$5000))



assuming BN is blank
the reason I am asking is that the only ways I know to get an "#value with
sumproduct is to have a #Value error in one of the cells or when there are
unequal arrays.

"Jimbo" wrote:

Thanks bj,

What I get with both of these is"0".

Jim

"bj" wrote:

is it just the AN column array you are having problems with, or the full
equation?
try a
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000))
if you get a 1 try
=sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000))
if you get more that 1 try reducing the range until you do get 1

"Jimbo" wrote:

Greetings,
Im looking for an explanation for something happening in Excel that I dont
understand. In a worksheet cell I have a formula
€œ=SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=€Asia€),--([Data.xls]A!$AN$2:$AN$5000))€
which works fine. Regardless of how I try to do it, I cant copy it to any
cell on any other worksheet without an error €œ#VALUE!€ rising from the
€œ--([Data.xls]A!$AN$2:$AN$5000))€ array. I dont understand why I get an
error when I know the formula works fine.
Appreciate whatever help you can give me!
Jim

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct Peter B Excel Worksheet Functions 1 March 7th 05 02:59 PM
sumproduct causing memory errors? dave Excel Worksheet Functions 1 March 3rd 05 10:31 AM
Can I reference =, <, or > sign in SUMPRODUCT BobT Excel Discussion (Misc queries) 7 February 16th 05 02:58 PM
Sumproduct function not working Scott Summerlin Excel Worksheet Functions 12 December 4th 04 06:15 AM
adding two sumproduct formulas together ski2004_2005 Excel Worksheet Functions 1 November 12th 04 10:08 PM


All times are GMT +1. The time now is 08:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"