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