arrayformula sum in Google spreadsheet

Question

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

=sum(A1:H1)

I need to go down 1000 rows.

This is a very simple question that does not need complication. Let us not complicate this. Simply put parenthesis in each of the array in the range. =arrayformula( Sum( (A1:A1000):(H1:H1000) ) — JP Alpano, Nov 24 '15 at 08:47
=arrayformula( Sum( (A1:A1000):(H1:H1000) ), while simple, does not work in sheets as of today. It seems to sum the entire range A1:H1000. — Eivind Eklund, Apr 04 '17 at 10:49
as a general matter, ArrayFormula will not compute correct results when composed around functions which themselves accept Range arguments. The range argument is "grabbed" by the function with with ArrayFormula is being composed (the function used as an argument to ArrayFormula), and used it its computation, returning its result (often a scalar). ArrayFormula will never see the input range, and have no capability to substitute for it as desired. This is why it fails to work with Or(), And(), etc. — DavidM, Jun 21 '17 at 00:31
Created sample Google Sheet for experiments https://docs.google.com/spreadsheets/d/1SfZwhA70jfXqMhb1cBZVvrioGuqYRD_DP4qJm_FvSvc — Pavel 'Strajk' Dolecek, Mar 22 '20 at 13:03

score 47 · Accepted Answer · edited Jul 12 '20 at 22:14

47

Another option:

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

edited Jul 12 '20 at 22:14

Koray Tugay

22,894
45
188
319

answered Feb 15 '14 at 22:58

AdamL

23,691
6
68
59

You're amazing. Worked. I'm using the new Google Spreadsheets now. If I change the formula to a more generic `=ArrayFormula(SUMIF(IF(COLUMN(A1:H),ROW(A1:A)),ROW(A1:A),A1:H))` the top right loading bar just keeps going and never loads. Why is that? Too many calculations for even the new Google spreadsheet? – jason Feb 16 '14 at 00:54
2

This is an issue I have previously escalated. IMO, array formulae referencing large arrays seem to have noticeably poorer performance in new Sheets compared with old. When referencing whole columns like this, try to ensure you have as few empty rows as possible. – AdamL Feb 16 '14 at 03:32
Any idea on when they will get `importrange()` working on the new Google Spreadsheet? – jason Feb 16 '14 at 06:47
2

I don't understand why you're using a range for `column()`. Won't it always be 1? – skube Oct 14 '15 at 14:37
Can you please explain why this works? Why can't you just do `=ArrayFormula(Sum(A1:H1))`? – Merlin -they-them- Aug 15 '18 at 04:23
1

Because `=ArrayFormula(...)` has no idea weather to output a row, or column vector. For example; `=Sum(A1:A5)` totals a row, `=Sum(A1:E1)` totals a column, `=Sum(A1:E5)` totals a field outputting 1 result. Putting `ArrayFormula` around it can do nothing to a single number. – Shuba Nov 22 '18 at 21:39

score 15 · Answer 2 · edited Mar 22 '17 at 02:56

15

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

edited Mar 22 '17 at 02:56

Rubén

34,714
9
70
166

answered Dec 22 '14 at 02:11

2

Meanwhile, the answer is totally useful, more so, than the other answers. Why bother trying to memorize or look up a convoluted formula pattern when all one need do is this: `=ARRAYFORMULA(IF(test for data in rows, A2:A+B2:B))` – Dec 29 '14 at 18:22
1

Oh well. It works for anyone who lives by the motto "time is money." – Dec 29 '14 at 18:56
yeah but technical debt is also (negative) money. – Mario Aug 06 '15 at 13:27
3

As your solution works good for 8 columns, it's not that useful while there are 100 columns. – Krzysztof Dołęgowski Dec 13 '20 at 10:26

player0 · Answer 3 · 2022-09-19T21:33:45.510

15

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

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

edited Sep 19 '22 at 21:33

answered Sep 19 '22 at 21:27

player0

124,011
12
67
124

4

wow. `LAMBDA`? looks like they also added `MAP` that's sick. – jason Sep 20 '22 at 18:32
2

@jason Yes, no more guessing about whether ArrayFormula is going to work as you expect! It should also be noted that `MAP` applies to the entire 2d range, so that's handy if you want to transform every individual value. – General Grievance Sep 26 '22 at 16:14
2

ARRAYFORMULA is great. LAMBDA is superb. – vesszabo Dec 21 '22 at 11:32
2

I've just learned a whole level of behaviour in Google Sheets from this answer. I had no idea Sheets could be so functional. – Mehmet Karatay Mar 02 '23 at 09:35

score 9 · Answer 4 · edited Apr 13 '17 at 12:57

9

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.

edited Apr 13 '17 at 12:57

Community

1
1

answered Feb 15 '14 at 20:38

Jacob Jan Tuinstra

1,197
3
19
50

6

In new Sheets, MMULT will fail if blank cells are in the array. The workaround is to wrap the first argument in `VALUE()`. – AdamL Feb 15 '14 at 22:56
for general propose maybe it easer to not repeat the range and just write `MMULT(A1:H,{1;1;1;1;1;1;1;1})` – pery mimon May 18 '20 at 16:12

score 1 · Answer 5 · edited Mar 22 '17 at 02:55

1

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

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

edited Mar 22 '17 at 02:55

Rubén

34,714
9
70
166

answered Jan 11 '17 at 16:06

Robert M. Münch

189
10

score 0 · Answer 6 · answered Aug 06 '15 at 14:04

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

SErik · Answer 7 · 2020-07-12T22:13:05.943

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

Max Makhrov · Answer 8 · 2020-11-30T16:37:58.660

0

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

edited Nov 30 '20 at 16:37

answered Nov 30 '20 at 16:18

Max Makhrov

17,309
5
55
81

score 0 · Answer 9 · answered Nov 30 '20 at 16:32

0

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

answered Nov 30 '20 at 16:32

Max Makhrov

17,309
5
55
81

score 0 · Answer 10 · answered Mar 22 '22 at 00:54

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)

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

score -3 · Answer 11 · edited Mar 22 '17 at 02:56

-3

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

edited Mar 22 '17 at 02:56

Rubén

34,714
9
70
166

answered Nov 24 '15 at 08:47

JP Alpano

118
1
6

If one of the columns have non-numeric values instead of returning an error, returns a number. Do you know why? – Rubén Feb 12 '16 at 04:50
2

This doesn't seem to work in google sheets. Maybe something changed over the past year but now it is perfectly valid to have `=SUM(($B1:$B10):($D1:$D10))` which is what probably broke the `ARRAYFORUMLA` wrapper. – TheZ May 13 '16 at 21:33
2

Google Sheets evaluates `(A1:A):(H1:H)` as `(A1:H)` when in an arrayformula :( – Shuba Nov 22 '18 at 21:45

arrayformula sum in Google spreadsheet

11 Answers11

Linked