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

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

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

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

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

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

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

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

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