Is there an Array Formula to Sum Multiple Columns in Google Sheets?
I have data in hundreds of rows and several columns in one of my Google Sheets files. I want to get the total of columns in each row (row-wise) using a single expanding array formula.
The SUM function may not yield expanded results even if you use the ArrayFormula function with it.
You can use MMULT or BYROW as array formulas to sum multiple columns (to get an expanded SUM) in Google Sheets. With this approach, a single formula can sum the total of columns in each row.
I believe I should make this clearer before going into more in-depth details or further elaborating on this topic.
For example, I have data in columns A to J, and the columns to sum are B to J, starting from row 2.
See the screenshot below to understand what I have been talking about.
Array Formula to Sum Multiple Columns: Non-Flexible
One option is to add each column individually and apply it as an array formula.
In the above example, you can insert the following formula in cell K2 to get the row-wise total:
=ArrayFormula(B2:B+C2:C+D2:D+E2:E+F2:F+G2:G+H2:H+I2:I+J2:J)However, this can cause problems when you insert a column within the range B2:J, as that column will not be automatically added to the formula. You would need to edit the formula to include the new column.
This is where the following flexible approach becomes relevant. We can use MMULT or BYROW as flexible array formulas to sum multiple columns, row-wise, in Google Sheets.
Array Formula to Sum Multiple Columns: Flexible
MMULT:
You can use the following MMULT formula in cell K2 to get the row-wise total of columns B to J:
=ArrayFormula(MMULT(N(B2:J),TRANSPOSE(COLUMN(B2:J)^0)))For quick reference, here is the syntax of the MMULT function:
MMULT(matrix1, matrix2)The purpose of the MMULT function is to calculate the matrix product of two matrices. But what we want is the sum.
In the above formula:
matrix1isN(B2:J). The purpose of the N function is to return numbers and 0 for non-numeric values and empty cells in the provided range. This prevents MMULT from returning a#VALUEerror, as it expects numeric values.matrix2should be a vertical array of 1s with the same length as the specified range. So we usedTRANSPOSE(COLUMN(B2:J)^0).
Where:
COLUMN(B2:J)returns the column numbers of the range B2:J (which are {2, 3, 4, 5, 6, 7, 8, 9, 10}).COLUMN(B2:J)^0returns an array of 1’s of the same size as the range {1, 1, 1, 1, 1, 1, 1, 1, 1}.
Note: You can replace matrix2 with the following formula as well: SEQUENCE(COLUMNS(B2:J2), 1, 1, 0)
BYROW:
Instead of MMULT, you can use the following BYROW formula in cell K2 to get the total of multiple columns in the range B2:J:
=BYROW(B2:J, LAMBDA(row, SUM(row)))BYROW is one of the lambda helper functions. We can use it to apply a regular SUM formula across each row in a specified range.
Can you explain this formula to me?
I know Lambda functions can be quite confusing. Let me explain the above formula step-by-step so you can understand it:
For example, to sum the columns B2:J2, you can use the following SUM formula:
=SUM(B2:J2)You can convert this to a custom function using LAMBDA and apply it to each row in the range using BYROW.
The custom LAMBDA function is:
LAMBDA(row, SUM(row))Tip: This is equivalent to the above SUM formula. To test it standalone, use:
=LAMBDA(row, SUM(row))(B2:J2)Syntax of BYROW:
BYROW(array_or_range, lambda)Since we want to sum multiple columns in the range B2:J, the array_or_range will be B2:J. We have already defined the LAMBDA function above.
The BYROW function applies the LAMBDA function to each row in the array_or_range.
Remove Zeros from the Sum of Multiple Columns
All the above formulas may leave trailing 0’s since we used an open range (B2:J), not a limited range (B2:J11).
To remove these zeros, use the following syntax:
=ArrayFormula(LET(total, sum_multiple_columns, IF(total=0,,total)))In this formula, replace sum_multiple_columns with one of the formulas provided above.
Example:
=ArrayFormula(LET(total, BYROW(B2:J, LAMBDA(row, SUM(row))), IF(total=0,,total)))
 in Google Sheets.){kind=link}
Hi Prashanth,
That worked beautifully! Thank you so much!
Is there a way to apply this technique if the first matrix has a varying length for each row? For example, I have a delimited list in column M, where I must split the list, get the value of the leftmost characters up to the first space, and add those values together so that I have a total for each row.
This formula gets me the split results, but I am stuck on how to get a total for each row’s resulting array using a single array formula at the top of my sheet.
=ARRAYFORMULA(IFERROR(VALUE(LEFT(TRIM(SPLIT(M2:M, ",")),FIND(" ", TRIM(SPLIT(M2:M, ",")))-1))))
Example: M2 = “2.50 This is a test, .50 Here is another item, .25 My 3rd selection” and the corresponding total is 3.25
M3 = “2.50 XYZ, .50 ABC” and the corresponding total is 3.
Hi Amy,
I believe you are familiar with LAMBDA functions. You can use the BYROW LAMBDA to solve this.
I am currently updating my old posts. There are over 1,200 of them, so it may take some time.
Here is my proposed formula:
=ARRAYFORMULA(LET(result,BYROW(IFERROR(VALUE(LEFT(TRIM(SPLIT(M2:M, ",")), FIND(" ", TRIM(SPLIT(M2:M, ",")))-1))),
LAMBDA(r,sum(r))),IF(result=0,,result)))
Is there a way to do this without specifying the number of rows?
Hi, Robert.
I guess you were talking about replacing B2:J11 with B2:J in the below formula.
=ArrayFormula(MMULT(B2:J11,(transpose(COLUMN(B1:J1)^0))))If so, try this.
=ArrayFormula(iferror(((1/MMULT(n(B2:J),(transpose(COLUMN(B1:J1)^0))))^-1)))Another way in the above example could be;
=ArrayFormula(MMULT(N(B2:J12), SEQUENCE(COLUMNS(B:J),1,1,0)))A similar one.
=MMULT(D99:J114, transpose(SPLIT(REPT("1,",COLUMNS(D99:J99)),",")))