This post is about creating a total column in Google Sheets using an array formula.

For that purpose, you can depend on the functions MMULT, Query, or the ‘+’ Sign. All these can sum each row in a table in Google Sheets.

Also, there is a new possibility of using one of the Lambda functions. So creating a column that contains the total in each row is easy.

To sum values, as you may already know, you can use the SUM function. 

It takes an array but won’t return an array. I mean, it won’t give you an expanding array result.

So, to sum each row in Google Sheets, you need to look for other functions, a Lambda, or function combinations, as mentioned above.

I am using function combinations in this tutorial. So I’m unable to explain every function involved in the combo. So please use my Google Sheets Functions Guide for reference.

To sum each row ‘manually’ in Google Sheets, you can use the below Sum formula in cell I2 and drag it down (see the GIF below).

=sum(C2:H2)

It’s not an array formula.

You can’t include the whole rows in a single formula this way.

That’s why I entered the formula in a single cell and copied it down.

Let me start with the best solution (array formula) to add a column with the total in each row in Google Sheets.

The Ultimate Solution to Sum Each Row in Google Sheets

We can take the above data as our example. I want to sum the columns C to H in each row.

Steps:

Empty column I, and in cell I1, enter the below MMULT formula. It is a flexible solution to sum each row in Google Sheets. It covers the range C2:H.

={"Total";arrayformula(if(len(A2:A),mmult(n(C2:H),transpose(sign(column(C:H)))),))}

You can insert columns between C to H, and the above MMULT formula would include the values in the newly inserted columns in the total.

You can adapt this formula for your data range.

Inside the MMULT, you can include your columns to sum. But take care of the formula if(len(A2:A),.

I have some text values in A2:A. I have used the Len function to limit the MMULT output to the last row containing a value in A2:A.

The above Len formula is equal to the below logical test. So you can use either of the ones.

if(A2:A<>"",

See the same MMULT in a new data range to understand it better. This time I am only using two-column data.

MMULT is not the only formula to sum values in multiple columns in each row.

Here are the alternative solutions, but they are not flexible except for the Lambda given at the end.

When you insert any new column between the range/array, they won’t adjust the range to sum (C2:H in the first example and B2:C in the second).

But it does not apply to the Lambda solution.

How to Add Total Column Using Query in Google Sheets

You can very quickly add a total column to a range using Query.

=Query(A1:H,"Select C+D+E+F+G+H label C+D+E+F+G+H'Total'",1)

I am using the same sample data which you have seen in the first example here.

This Query formula in cell I1 will place a total column. Now let me show you why this formula is not flexible.

See, I have inserted a new column, and our range to sum is now C2:I.

If you try MMULT and Query, you can understand that MMULT returns the correct total. The Query will fail in this case.

But Query has one advantage. If you want, you can get multiple-column output.

=Query(A1:H,"Select A,C+D+E+F+G+H label C+D+E+F+G+H'Total'",1)

This formula will return a two-column output in which the first column will be the “Item Desc” and the second one the “Total”.

You can specify the extra column that you want to include in the Select clause in Query.

That doesn’t mean you can not include an additional column in MMULT. That is possible, but simple to do using Query.

Sum Each Row Using the ‘+’ Sign in Google Sheets

Here is one more array formula to get the total in each row. This formula is also for cell I1. It will also expand down the column.

={"Total";ArrayFormula(if(len(A2:A),C2:C+D2:D+E2:E+F2:F+G2:G+H2:H,))}

When you insert a new column, you must edit this formula to include it.

That means this follows the same flexibility issue we faced in Query.

Sum Function with Byrow – Recommended

The regular Sum formula may not spill its result. But by using BYROW, we can make it spill down. Here is how.

={"total";byrow(C2:H,lambda(r,if(counta(r)=0,,sum(r))))}

This formula is as per the sample data in the first screenshot (example). So it must be keyed in cell I1.

You can see the presence of the COUNTA function in the BYROW Lambda formula.

It’s to omit sum results in rows, where there is no value in count due to blank.

There is one more dynamic formula to sum each row in Google Sheets.

That is using a database function which I have included in the first tutorial below.

Additional Resources:

• How to Use the Sumif Function Horizontally in Google Sheets.
• Sum Multiple Columns Dynamically Across Rows in Google Sheets.
• How to Use MIN in Array in Google Sheets for Expanded Results.
• How to Find Max Value in Each Row in Google Sheets [Array Formula].
• Average Array Formula Across Rows in Google Sheets.
• PRODUCT Array Formula for Row-Wise Products in Google Sheets.
• How to Expand Count Results in Google Sheets Like Array Formula Does.