Short answer
Use ARRAY_CONSTRAIN(array,row_limit,column_limit)
Explanation
ARRAY_CONSTRAIN function could return the number of rows and columns that you want, just set the row_limit and column_limit parameters accordingly. To automatically set this parameters you could use a function like COUNT or COUNTA.
To calculate the number of rows/columns considering the data range by using built-in functions,
for rows use a formula like the following
=MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0))
for columns use a formula like the following
=MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))
The sample formula in the question will look like the following:
=ARRAYFORMULA(
ARRAY_CONSTRAIN(
MMULT(N(ROW(A2:A)>=TRANSPOSE(ROW(A2:A))),SIGN(ROW(A2:A))),
MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0)),
MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))
)
)
=SUM(MAX(A1,B1)+MAX(A2,B2)+MAX(A3,B3))
This works only for 3 rows.
A general solution would be following,
In a new column (say C) put this formula
=MAX(A1,B1)
Then apply this formula on some other cell.
=SUM(C:C)
Best Answer
Here is an implementation of the same
mmultapproach in Google Sheets. Your problem may have been because of the lack ofarrayformula.Here A1:F9 is the array being processed, and ">10" is the condition. So,
N(A1:F9 > 10)returns a 0-1 array of the same size. It is summed by rows usingmmult. The second argument is a column of 1s; I useiferror(A1:F1/0, 1)to generate an array of 1s of the same size as A1:F1.Finally,
countifcounts positive entries of the product, i.e., the rows with at least one positive number.