The ROW () function behaves differently inside SUM () and SUMPRODUCT ()

Question

Definition of the problem:

Enter any number in cell A1 . Now try the following formulas anywhere on the first line.

 =SUM(INDIRECT("A"&ROW())) 

and

 =SUMPRODUCT(INDIRECT("A"&ROW())) 

The first formula is evaluated, the second is #VALUE error. This is because the ROW() function behaves differently inside SUMPRODUCT() .

In the first formula, ROW() returns 1 . In the second formula, the string returns {1} (an array of the same length), although the formula was not entered as a CSE formula.

Why is this happening?

Background

I need to evaluate a formula like

 =SUMPRODUCT(INDIRECT(*range formed by concatenation and using ROW()*)>1) 

This is a bug fix. As a workaround to this problem, I now compute ROW() in another cell (on the same line, obviously) and combine it inside INDIRECT() . As an alternative, I also tried to encapsulate it inside a sum function, like SUM(ROW()) , and this works too.

I would appreciate it if someone could explain (or tell me a resource that can explain) why ROW() returns an array inside SUMPRODUCT() without entering CSE.

Answer 1

Interest Ask. There are subtle issues here that I have not seen documented.

It seems that INDIRECT("A"&ROW()) returns an array of one element, which is a reference to a cell, not a value in that cell. Many functions cannot correctly resolve this data type, but some functions, such as N and T, can "play out" the array and return the base value.

Take this case when there are two elements in an array:

 =SUM(N(INDIRECT("A"&ROW(1:2)))) 

This returns A1 + A2 when an array is entered, but it returns A1 only when input. However, changing the ROW (1: 2) to {1; 2} in this formula returns the correct result when typing in the usual way. The equivalent SUMPRODUCT formula returns A1 + A2 whether the array is entered or not.

This may be due to the way the arguments are registered in the function. According to http://msdn.microsoft.com/en-us/library/bb687900.aspx there are two methods for registering function arguments for processing Excel data types:

R / U type: "Values, arrays, and range references."

P / Q type: "Excel converts unicellular references to simple values ​​and multicellular references to arrays when preparing these arguments."

The SUM arguments seem to match the R / U type, and the SUMPRODUCT arguments behave like the P / Q type. An array introducing the SUM formula above causes the range reference argument in ROW to be evaluated as an array, whereas this happens automatically with SUMPRODUCT.

Update

After a bit more research, this is another piece of evidence that could 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 allows us to use ranges for one area, such as !R1C1:!R2C1 . On the other hand, changing 4 (xlfsum) to 228 (xlfsumproduct) allows only references to one area, which it calls in the same way as SUMPRODUCT.

Answer 2

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

Then you have an example: =SUMPRODUCT(INDIRECT("A"&INDEX(ROW(),1)))

Answer 3

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

Many functions or combinations of them return arrays - you do not need CTRL + SHIFT + ENTER for this to happen, you only need CSE in many cases to process the created arrays.

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

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

.... expanding this value to your range, this formula will count the number of values> 1 in the 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 the formulas

you can use SUMPRODUCT or COUNTIFS in the same way if there are additional conditions for adding