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