This tutorial shows you how to use an Array Formula to Sum Multiple Columns in Google Sheets. Along the way, you’ll also learn how to include or exclude specific columns based on their headers.

I have data spanning hundreds of rows and several columns in one of my Google Sheets files. I want to get the total of multiple columns for each row using a single, expanding array formula. Additionally, I want to be able to include or exclude specific columns in the calculation.

The regular SUM function doesn’t expand automatically—even with ARRAYFORMULA. And it doesn’t let you dynamically include or exclude columns by their headers.

Instead, you can use MMULT or BYROW as array formulas to sum multiple columns. These allow you to calculate row-wise totals with a single formula. We’ll also use FILTER to manage dynamic column inclusion or exclusion.

Scenario Explained

Before diving into details, let me clarify what we’re solving.

Let’s say you have data in columns A to J, and you want to sum columns B to J starting from row 2. How do you get the total for each row using a single array formula in cell K2? Also, what if you want to exclude specific days or columns?

Here’s a screenshot to illustrate what I mean:

Excluding specific columns (like weekends) can be important—for instance, when calculating employee work hours. This method gives you the flexibility to include or exclude such columns easily.

Array Formulas to Sum Multiple Columns in Google Sheets

MMULT Formula

Use this formula in cell K2 to sum columns B to J for each row:

=ArrayFormula(
   LET(
      cols, B2:J, 
      total, MMULT(N(cols), SEQUENCE(COLUMNS(cols), 1, 1, 0)), 
      total
   )
)

MMULT syntax:

MMULT(matrix1, matrix2)

What it does:

• matrix1 is N(cols) → This converts values in cols (i.e., B2:J) to numbers, turning blanks or text into 0. This avoids errors.
• matrix2 is a vertical array of 1s → SEQUENCE(COLUMNS(cols), 1, 1, 0) creates that array.

Note: When using an Array Formula to Sum Multiple Columns, you may get trailing zeros in empty rows. We’ll fix that below.

BYROW Formula

Another method is to use the modern BYROW function:

=LET(
   cols, B2:J, 
   total, BYROW(cols, LAMBDA(row, SUM(row))),
   total
)

What it does:

• BYROW applies the formula LAMBDA(row, SUM(row)) across each row in the range B2:J.
• This is like writing =SUM(B2:J2) for each row—but dynamically.

To test the lambda alone:

=LAMBDA(row, SUM(row))(B2:J2)

Syntax:

BYROW(array_or_range, lambda_function)

Remove Zeros from the Sum of Multiple Columns

If you’re summing an open range like B2:J, trailing rows may show 0 even when empty.

Fix for MMULT:

Replace total with an IF condition:

=ArrayFormula(
   LET(
      cols, B2:J, 
      total, MMULT(N(cols), SEQUENCE(COLUMNS(cols), 1, 1, 0)), 
      IF(total, total, "")
   )
)

Fix for BYROW:

Update the lambda to ignore blank rows:

=LET(
   cols, B2:J, 
   total, BYROW(cols, LAMBDA(row, IF(COUNT(row), SUM(row), ""))),
   total
)

Include or Exclude Specific Columns

If you want to include or exclude specific columns from the total based on their headers, you can do that by dynamically filtering the column range.

For example, let’s say:

• Your data is in columns B2:J
• Row 1 (B1:J1) contains the column headers
• You want to exclude columns whose headers are listed in column O.

In that case, you should replace B2:J in your formula (whether using MMULT or BYROW) with the following filtered range:

FILTER(B2:J, ISNA(XMATCH(B1:J1, O1:O)))

This excludes any columns whose headers match those listed in column O.

If you want to include only specific columns listed in, say, column N, use:

FILTER(B2:J, XMATCH(B1:J1, N1:N))

This lets you control what’s summed without hardcoding column letters.

Similar Tutorials

1. How to Sum Each Row in Google Sheets – Covers all methods to sum across rows, including manual, BYROW, QUERY, MMULT, and more. It doesn’t cover column inclusion/exclusion by header.
2. Sum Multiple Columns Dynamically in Google Sheets – Dynamically sum across rows using start and end columns or start and end column headers.