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 are:
"#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,
> > > > 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
> > > >