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 said:
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 said:
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.
:
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.
:
Thanks bj,
What I get with both of these is"0".
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
:
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
