9

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.

playercharlie
  • 629
  • 1
  • 6
  • 17

3 Answers3

7

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.

lori_m
  • 5,487
  • 1
  • 18
  • 29
  • +1 This is exactly what I needed. The 'dereferencing' capability of `N()` and `T()` functions surprises me. In fact, in the formula `=SUMPRODUCT(N(INDIRECT("A"&ROW())))` , the `N()` function actually resolves a #VALUE error (as seen in the Evaluate Formula, Excel 2003). This is extremely good to know. Also the info on the R/U and P/Q types of arguments does seem to be a good explanation. The handling of the `xlTypeNil` elements for the P/Q types seems to match up with the behavior of `SUMPRODUCT()`. Thanks for a brilliant answer. Would've never known what to Google for. Deserves a +10!! – playercharlie Jun 21 '12 at 08:36
  • Glad this helped - Laurent Longre figured out this behavior originally and showed how you can use the CALL function with reference to xlcall.h for worksheet functions: http://www.cpearson.com/excel/Call.htm. In VBA this can still be accessed via: `ExecuteExcel4Macro`. – lori_m Jun 21 '12 at 10:04
2

As ROW() returns an array, use INDEX to get the 1st element.

You example then becomes: =SUMPRODUCT(INDIRECT("A"&INDEX(ROW(),1)))

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • Thanks for the answer. I used the `SUM()` function instead of the `INDEX()` function, and got around the problem. However, I was looking for an explanation as to why I needed to do this. – playercharlie Jun 21 '12 at 08:49
  • This is the best answer for me. The usage of INDEX() on the array resulted by ROW() used inside SUMPRODUCT() is perfect! If volatility is all about recalculation, it won't bother me so much because computer is much faster now. There are lots of indirect and offset used across my workbooks and I am OK with up to 1 sec delay of recalculation. So if I ever need to use ROW() inside SUMPRODUCT(), then INDEX() is a must helper function! Thank you so much SeanC – Rivo Feb 10 '18 at 17:26
  • Oh, and thanks for the SUM() trick self-answered by OP. It shortens the formula, and I kind of prefer the shorter one. I wonder if one of them is faster. Perhaps SUM() is faster because it's non volatile? – Rivo Feb 10 '18 at 18:06
  • `sumproduct` is not volatile. These are the volatile functions: https://msdn.microsoft.com/en-us/library/office/bb687891.aspx#Anchor_2 – SeanC Feb 11 '18 at 03:11
1

I don't think ROW() behaves differently here, it returns an array in both cases. I assume that SUM and SUMPRODUCT treat that array differently - not sure why.

Many functions or combinations of them return arrays - you don't need CTRL+SHIFT+ENTER to make that happen, you only need CSE in many cases to process the arrays created.

I would just use INDEX in place of INDIRECT (which also benefits you by avoiding a volatile function), i.e.

=SUMPRODUCT(INDEX(A:A,ROW()))

....expanding that to your range this formula will count the number of values > 1 in a range in column A where x defines the start row and y the end row

=COUNTIF(INDEX(A:A,x):INDEX(A:A,y),">1")

x and y can be calculated by formulas

you can use SUMPRODUCT or COUNTIFS in a similar way if there are more conditions to add

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Thanks for the answer. I used the `SUM()` function around the `ROW()` function, and got around the problem. Also, for my particular problem, I had to use the `INDIRECT()` function, because the cell references were being calculated else where!. You seem to have got it right when you say CSE just changes the processing of arrays. @lori_m has a terrific answer on why `SUM()` and `SUMPRODUCT()` treat the arrays differently. – playercharlie Jun 21 '12 at 08:46