Functions indirect and sumproduct

[Hall]

Suppose cells A1:A4 have values 1,2,3,4

If a cell has {=SUMPRODUCT(A1:A4)}, it gets the value 10.

If a cell has {=SUM(indirect(concatenate("a",row(A1),":a4")))}, it gets the
value 10.

But if a cell has {=SUMPRODUCT(indirect(concatenate("a",row(A1),":a4")))},
it gets the value 0.

Shouldn't that last case also have 10?

I've greatly simplified the examples for this question. I do have a purpose
for this formulation. But I seem to be unclear about this apparent
inconsistency.

Any help would be appreciated.

 

[Don Guillett]

Maybe I'm over simplifying it but won't
=sum(a1:a4) or =a1+b1+c1+d1
work?

 

[Hall]

Well, thanks but I did say I had a purpose for this formulation. I need to
do a lot more than just add the cells in an array.

I may have to repost this now...

 

[Dan E]

Hall,

First of all, none of the formulai you posted needed to be
array entered to work. I couldn't get the sumproduct to work
either. Perhaps if you post your final goal, someone can
suggest an alternative.

Dan E

 

[Don Guillett]

By all means. Let us know what you really want to do and we will try to
help.

 

[Harlan Grove]

...
...

But if a cell has {=SUMPRODUCT(indirect(concatenate("a",row(A1),":a4")))},
it gets the value 0.

Shouldn't that last case also have 10?

...

This is a perversity in Excel's array formula semantics. It's been mentioned
before in one of the Excel newsgroups, but I'll leave a Google Groups search to
you. The screwy part is that ROW(A1) returns a single entry *array* rather than
just a single value, i.e., {1} rather than 1. That makes the CONCATENATE call
return the *array* {"A1:A4"}, which causes INDIRECT to return an array of range
references, {A1:A4}, which is *NOT* supported syntax. Note that

=SUM(INDIRECT("A"&ROW(A1)&":A4"))

works without a problem because it's used to taking arbitrary range arguments.
SUMPRODUCT chokes because it expects all its arguments to be either arrays of
numeric values or single area ranges, but {A1:A4} is neither.

You have to ensure that INDIRECT is fed a *scalar* value, i.e., a single text
string and not a single entry array containing a text string. That means
something like

=SUMPRODUCT(INDIRECT("A"&INDEX(ROW(A1),1)&":A4"))

That said, there's allmost certainly some way to use OFFSET rather than INDIRECT
that would bypass this problem entirely. What's the *actual* formula?

 

[Dana DeLouis]

I think this formula triggers Array formula evaluation. Therefore, it
requires one to use Transpose. I am not sure if this would work in Excel
97.

=SUMPRODUCT(INDIRECT(TRANSPOSE(CONCATENATE("A",ROW($A$1),":A4"))))

 

[Don Guillett]

Post does not mean attach files. If fact, Harlan expressly said

"Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives."

We strongly discourage attaching files. It is probable that it will NOT be
opened.
Suggested reading
http://www.mvps.org/dmcritchie/excel/posting.htm
http://www.cpearson.com/excel/newposte.htm
In the movie "Cool Hand Luke", I think there was a statement "we gotta learn
to get along", or something like that.

 

[Hall]

Sorry for the attachment guys.

Actually, Harlan gave me the formulation for what I want. He indirectly is
pointing out the inconsistency in sum and sumproduct.

Thanks all the your help!

Happy new year.

 

[Harlan Grove]

I think this formula triggers Array formula evaluation. Therefore, it
requires one to use Transpose. I am not sure if this would work in Excel
97.

=SUMPRODUCT(INDIRECT(TRANSPOSE(CONCATENATE("A",ROW($A$1),":A4"))))

....

Just when I thought I'd figured out Excel . . .

So transposing the textref makes this work,

=SUMPRODUCT(INDIRECT(TRANSPOSE("A"&ROW($A$1)&":A4")))

but failing to do so doesn't work,

=SUMPRODUCT(INDIRECT("A"&ROW($A$1)&":A4"))

Which means TRANSPOSE({"A1:A4"}) is somehow different than {"A1:A4"}). I
suppose this is due to implicit array iteration in the formula
parser/evaluator, but even so it's difficult to see how this works. Yet
another mystery I'll have to live with.

 

[Harlan Grove]

Actually, Harlan gave me the formulation for what I want. He indirectly is
pointing out the inconsistency in sum and sumproduct.

....

Even when SUM and SUMPRODUCT are both fed a single argument, they do
slightly different things, so 'inconsistency' may be too strong a term for
this.