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,
> I’m looking for an explanation for something happening in Excel that I don’t
> 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 can’t 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 don’t understand why I get an
> error when I know the formula works fine.
> Appreciate whatever help you can give me!
> Jim
>