Google-sheets – Sum over a formula in Google Sheets

formulasgoogle sheetsgoogle-sheets-query

I have a Google Sheet and I need to add a lot of stuff like

(ABS(A1)+A1)/A1+ (ABS(A2)+A2)/A2+...+ (ABS(A10)+A10)/A10

but I don't want to write out the sum each time for each term A1,A2,A3,…, A10

Is there a way to write the sum more concise? like

SUM( (|x| + x)/x : for x from A1 to A10)

I know that's not correct but that is what I want.

Best Answer

You can use an Arrayformula to achieve your goal.

You don't need to drag down a formula nor specify cells. This will work for every correct calculation in column A:

=Sum(Arrayformula(Iferror((Abs(A2:A8)+A2:A8)/A2:A8,"")))

or ignore iferror if you are sure A2:A8 contains numbers (and not text or blank elements):

=Sum(Arrayformula((Abs(A2:A8)+A2:A8)/A2:A8))

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))
   )
)

Best Answer

Related Solutions

Google-sheets – Getting SUM function to work in Google Spreadsheets

Google-sheets – How to block automatic row and column insertion in Google Sheets

Short answer

Explanation

Related Topic