arrayformula sum in Google spreadsheet

Question

How do you arrayformula() a sum() such as:

=sum(A1:H1)

I need to go down 1000 rows.

Answer 1

Another option:

=ArrayFormula(SUMIF(IF(COLUMN(A1:H1),ROW(A1:A1000)),ROW(A1:A1000),A1:H1000))

Answer 2

Of the two answers that work, Jacob Jan Tuinstra and AdamL, Jacob gives a better answer. Jacob's runs faster and is easier to remember.

However, why use crazy formulas when it is much easier to use Google Sheets to the fullest?

=ARRAYFORMULA(A2:A+B2:B+C2:C+D2:D+E2:E+F2:F+G2:G+H2:H) 

In the foregoing formula, you can use named ranges instead of the ranges by reference.

=ARRAYFORMULA(range1+range2+range3+range4+range5+range6+range7+range8) 

As well, you can sum across rows that span sheets rather than being stuck working with columns within the same sheet.

To fix the formula to block returning zeros for blank rows, use this:

=arrayFormula(if(isNumber(A2:A),A2:A+B2:B+C2:C+D2:D+E2:E+F2:F,G2:G,H2:H))

See: See Ahab's answer on Google Forums

For a cool sum accumulation formula, see Otávio Alves Ribeiro's answer on Google Forums

Answer 3

This is what you are looking for:

=MMULT(A1:H1000,TRANSPOSE(ARRAYFORMULA(COLUMN(A1:H1000)^0)))

See this answer on Web Application I gave: https://webapps.stackexchange.com/a/53419/29140

Note: tried it on the new Google Spreadsheet, without succes.

Answer 4

If you want to repeat the sum on all the bellow line you could use arrayformula(A:A+H:H)
If you want to sum everything then you don't need array formula, just use sum(A:A;H:H)

Answer 5

if I look at this formula I really think the following might be simpler. Add this to Tools > Script Editor:

function row_sum(range_to_sum_per_row) {
  var result_column = [];
  for (var r = 0; r < range_to_sum_per_row.length; r++) {
    var row_sum = parseFloat(0);
    for (var c = 0; c < range_to_sum_per_row[r].length; c++) {
        row_sum += range_to_sum_per_row[r][c] || 0;
    }
    result_column.push([row_sum]);
  }
  return result_column;
}

use this like so for performance reasons, where C:H is the range you want to sum up and A:A is a column that does not contain an empty string:

=row_sum(filter(C2:H, len(A2:A)>0))

Answer 6

Summing A-H horizontal and running down for 523 lines:

=ARRAYFORMULA(iferror(mmult(A1:H523;TRANSPOSE(column(A1:H1))^0)))

Answer 7

If you want to be able to add rows and sum to the last row for all values in A1:H, you can use:

 =ArrayFormula(SUMIF(IF(COLUMN(A1:H1),ROW(A1:A)),ROW(A1:A),A1:H))

Alternatively, if you want be be able to add rows and columns to the spreadsheet and sum to the last of both this can also be done. Paste the following code into any cell and it will create a column of summed values for all cells in each row below and to the right of pasted cell:

=arrayformula(SUMIF(IF(COLUMN(indirect(concatenate(REGEXREPLACE(address(row(),column()+1),"[^[:alpha:]]", ""),VALUE(REGEXREPLACE(address(row(),column()),"[^[:digit:]]", "")),":",VALUE(REGEXREPLACE(address(row(),column()),"[^[:digit:]]", ""))))),ROW(indirect(concatenate(REGEXREPLACE(address(row(),column()+1),"[^[:alpha:]]", ""),VALUE(REGEXREPLACE(address(row(),column()),"[^[:digit:]]", "")),":",REGEXREPLACE(address(row(),column()+1),"[^[:alpha:]]", ""))))),ROW(indirect(concatenate(REGEXREPLACE(address(row(),column()+1),"[^[:alpha:]]", ""),VALUE(REGEXREPLACE(address(row(),column()),"[^[:digit:]]", "")),":",REGEXREPLACE(address(row(),column()+1),"[^[:alpha:]]", "")))),indirect(concatenate(concatenate(REGEXREPLACE(address(row(),column()+1),"[^[:alpha:]]", ""),VALUE(REGEXREPLACE(address(row(),column()),"[^[:digit:]]", "")),":"),address(rows($A:$A),columns($1:$1))))))

Answer 8

Using Query

=INDEX(TRANSPOSE(
  QUERY(TRANSPOSE(FILTER(A2:H,A2:A<>"")),
  "select sum(Col"&JOIN("), sum(Col",SEQUENCE(COUNTA(A2:A)))&")",0)
),,2)

notes:

• generating query string on the fly

Answer 9

Using DSUM:

=ARRAYFORMULA(DSUM(
  TRANSPOSE(FILTER({A2:A,A2:H},A2:A<>"")),
  SEQUENCE(COUNTA(A2:A)),{IFERROR(1/0);IFERROR(1/0)}))

notes:

• {IFERROR(1/0);IFERROR(1/0)} is to make zero creteria for DSUM.

• {A2:A,A2:H} -- added fake column for DSUM to mimic header column.

• may be able to cahnge the formula into DMAX or DAVERAGE

Answer 10

Let us not complicate this. Simply put parenthesis in each of the array in the range.

=arrayformula( Sum( (A1:A):(H1:H) )

This spans not only upto 1000 rows but upto infiinity.

If you really want to limit then go

=arrayformula( Sum( (A1:A1000):(H1:H1000) )