CODEWITHSUNDEEP
google-sheets
jason
jason

Reputation: 4429

arrayformula sum in Google spreadsheet

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

=sum(A1:H1)

I need to go down 1000 rows.

Upvotes: 47

Views: 97879

Answers (11)

player0
player0

Reputation: 1

with new functions in google sheets (since 20 Sep, 2022) all you need is:

=BYROW(A:H; LAMBDA(x; SUM(x)))

enter image description here

Upvotes: 31

Murta
Murta

Reputation: 2195

Answer similar to @adamL suggestion, but removing the internal if.

=ArrayFormula(
     sumif(ROW(A1:A10)*COLUMN(A1:H1)^0,ROW(A1:A10),A1:A10)
 )

In this case I use ROW(A1:A10) * COLUMN(A1:H1)^0 to generate the row number matriz. To understand how it works, you can test just this part in your Google sheets:

= ArrayFormula(ROW(A1:A10) * COLUMN(A1:H1)^0)

Row number matrix

So, with your row matrix, sumif can operate for each line, is the line has the matched row number, it will be summed up.

Upvotes: 0

Max Makhrov
Max Makhrov

Reputation: 18697

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

Upvotes: 0

Max Makhrov
Max Makhrov

Reputation: 18697

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

Upvotes: 0

AdamL
AdamL

Reputation: 24599

Another option:

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

Upvotes: 49

SErik
SErik

Reputation: 11

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

Upvotes: 0

user4305191
user4305191

Reputation:

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

Upvotes: 16

JP Alpano
JP Alpano

Reputation: 120

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

Upvotes: -2

Robert M. M&#252;nch
Robert M. M&#252;nch

Reputation: 319

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

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

Upvotes: 1

Mario
Mario

Reputation: 2639

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

Upvotes: 0

Jacob Jan
Jacob Jan

Reputation: 1197

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.

Upvotes: 9

Related Questions