SUMPRODUCT

[Jimbo]

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

[Alok]

I tried it in XL2K but could not reproduce the error.

Alok Joshi

"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
>

[bj]

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
>

[Jimbo]

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
> >

[bj]

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
> > >

[Jimbo]

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
> > > >

[bj]

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 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
> > > > >

[Jimbo]

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 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
> > > > > >

[bj]

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 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
> > > > > > >