Is there a way to sum multiple columns in Google Sheets using an array formula? If you have data in hundreds of rows and several columns in a Google Sheets file, you may want to calculate the total of each row, column-wise. In this article, I will show you how to achieve this using the MMULT function in Google Sheets.
The Problem with the SUM Formula
The normal SUM formula in Google Sheets does not expand to the rows below, even when using the ArrayFormula function. This can be a problem when dealing with a large number of rows. While you can copy and paste the SUM formula into multiple cells, it is not a flexible solution.
Let’s take a look at an example to illustrate this. In the screenshot below, I am referring to column K, which represents the sum of columns B to J in each row.
K2 contains the total of the column range B2:J2. Using the regular SUM formula, the formula in K2 would be =sum(B2:J2). Even if you use the ArrayFormula, the SUM formula in K2 can’t expand to the rows below (=arrayformula(sum(B2:J2))).
Array Formula to Sum Multiple Columns
To overcome this limitation, we can use the MMULT function in Google Sheets. The MMULT function calculates the matrix product of two matrices. Here is one way to use it as an array formula to sum multiple columns in Google Sheets:
Array Formula 1:
=ArrayFormula(B2:B11+C2:C11+D2:D11+E2:E11+F2:F11+G2:G11+H2:H11+I2:I11+J2:J11)
This formula calculates the sum of each column and produces an expanding result. However, it has a few limitations. If the number of columns is large, entering the formula without making typos can be difficult. Additionally, if you insert new columns, you need to update the formula to include them.
Let’s explore a more flexible solution using the MMULT function.
Flexible Array Formula to Sum Multiple Columns
Instead of using the SUM function, we will use the MMULT function to achieve an expanding sum in Google Sheets. Here is the formula:
Array Formula 2:
=ArrayFormula(MMULT(n(B2:J11),(transpose(COLUMN(B1:J1)^0))))
In this formula, the cell range n(B2:J11) represents matrix1. The N function converts blank cells in the range to 0 (zero), ensuring that MMULT does not return an error. We generate a virtual column for matrix2 that contains only the number 1 to have no effect on the multiplication in MMULT.
Matrix1 has nine columns from B to J, so matrix2 has nine rows. This satisfies the core matrix multiplication principle that the number of columns for matrix1 must equal the number of rows for matrix2.
Grouping the Sum of Multiple Columns
Now that we know how to calculate the sum of multiple columns, let’s explore how to group and display these sums in Google Sheets.
Formula 3:
=QUERY({A2:A11,ArrayFormula(MMULT(n(B2:J11),(transpose(COLUMN(B1:J1)^0))))},"Select Col1, Sum (Col2) group by Col1 label Sum(Col2)''")
In this formula, the Query range is the Array Formula 2 (the formula within the curly brackets, excluding the A2:A11 range). The query formula has two columns: column A2:A11 contains the names we want to group, and the second column is the sum of the range B2:J11 calculated using the Array Formula 2.
By joining these two columns using curly braces, we create a two-column array that can be easily grouped and totaled using the QUERY function.
Conclusion
Using the MMULT function in Google Sheets, you can calculate the sum of multiple columns row-wise and group them as needed. Take some time to explore the possibilities of the MMULT function and experiment with different formulas to manipulate your data effectively in Google Sheets.
To learn more about array formulas and other Google Sheets functions, visit Crawlan.com for expert tutorials and guides.
