Problem definition:
Enter any number in cell A1
. Now try the following formulae anywhere on first row.
=SUM(INDIRECT("A"&ROW()))
and
=SUMPRODUCT(INDIRECT("A"&ROW()))
The first formula evaluates, the second one gives a #VALUE error.
This is caused by the ROW()
function behaving differently inside SUMPRODUCT()
.
In the first formula, ROW()
returns 1
. In the second formula, row returns {1}
(array of one length), even though the formula has not been entered as a CSE formula.
Why does this happen?
Background
I need to evaluate a formula of the type
=SUMPRODUCT(INDIRECT(*range formed by concatenation and using ROW()*)>1)
This is working out to an error. As a workaround to this issue, I now calculate ROW()
in another cell (in the same row, obviously) and concatenate that inside my INDIRECT()
. Alternately, I also have tried encapsulating it inside a sum function, like SUM(ROW())
, and that works as well.
I would sure appreciate it if someone could explain (or point me to a resource that can explain) why ROW()
returns an array inside SUMPRODUCT()
without being CSE entered.
Interesting question. There are subtle issues here which I haven't seen documented.
It seems INDIRECT("A"&ROW())
returns an array consisting of one element which is a reference to a cell - not the value in that cell. Many functions cannot resolve this type of data correctly but a few functions such as N and T can "dereference" the array and return the underlying value.
Take this case where there are two elements in the array:
=SUM(N(INDIRECT("A"&ROW(1:2))))
This returns A1+A2 when array entered but it only returns A1 when entered normally. However changing ROW(1:2) to {1;2} in this formula returns the correct result when entered normally. The equivalent SUMPRODUCT formula returns A1+A2 whether array entered or not.
This may be related to how the arguments are registered in the function. According to http://msdn.microsoft.com/en-us/library/bb687900.aspx there are essentially two methods to register function arguments to handle Excel data types:
Type R/U: "Values, arrays, and range references."
Type P/Q: "Excel converts single-cell references to simple values and multi-cell references to arrays when preparing these arguments."
SUM arguments seem to conform with type R/U while SUMPRODUCT arguments behave like type P/Q. Array-entering the SUM formula above forces the range reference argument in ROW to be evaluated as an array whereas this happens automatically with SUMPRODUCT.
Update
After a little more investigation, here's further evidence that might support this theory. Based on the link in the comment, the formula =SUM((A1,A2)) gives the same values as:
?executeexcel4macro("CALL(""Xlcall32"",""Excel4"",""2JRJR"",4,,1,(!R1C1,!R2C1))")
Registering the last argument as type P by changing 2JRJR
to 2JRJP
gives an error in this case but does allow for single area ranges like !R1C1:!R2C1
. On the other hand, changing the 4 (xlfsum) to 228 (xlfsumproduct) only allows single area references either way it's called just like SUMPRODUCT.
As ROW()
returns an array, use INDEX
to get the 1st element.
You example then becomes: =SUMPRODUCT(INDIRECT("A"&INDEX(ROW(),1)))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With