I have a dataset in Google Sheets with several columns of daily data. The first column name (field label) is “Description” and other column names are dates. I want to dynamically sum from 6th column to 11th column that across rows. How to sum multiple columns dynamically across rows in Google Sheets?

First, I will explain the question in detail. After that, we can think about the solution, right?

My sample dataset will look like as below. Please note that below under the formula section, I’ll use an even shorter dataset to capture GIF screenshots with small file sizes.

I want a sum formula in cell N2 that can sum multiple columns dynamically.

What I mean to say is, I will specify the start and end column numbers to include the columns in the sum dynamically. For that, I will use two cells, one for specifying the FROM column and the other for specifying the TO column.

In other words, sum columns from 5 (start column) to 7 (end column) means add the column ranges E2:E+F2:F+G2:G.

If I simply, in a non-dynamic way, want to sum the columns B to M (2 to 13), then I will use MMULT or Query.

I have explained that already. If you want you can check that here later – Array Formula to Sum Multiple Columns in Google Sheets and Grouping.

Here I want a slightly different thing. I want to sum multiple columns dynamically in Google Sheets.

Let’s consider a small dataset for example. Here is what I want, I mean dynamic sum of multiple columns across rows in Google Sheets, in action.

Cell H2: Starting column to use in the Sum.
Cell I2: Ending column to use in the sum.

These two cell references make my formula in cell F1 dynamic.

How to Sum Multiple Columns Dynamically in Google Sheets (Formula Section)

In the above live illustration, I have a Query formula in cell F1. That formula dynamically sums the columns across the range. As mentioned above, the dynamic column numbers are specified in cell H2 and I2.

Here is that Query formula in use in cell F1.

=QUERY({A1:E},"Select "&K1&" label "&K1&"'Total'")

A1:E is the data range in this formula. Then where is the said cell reference H2 and I2 in this formula?

See the cell reference K1 in the above Query formula. The cell K1 is a helper cell which contains the below formula.

=ArrayFormula(textjoin("+ ",TRUE,("Col"&row(indirect("A"&H2&":A"&I2)))))

This reveals where the said two cell references are in use.

I will explain this K1 formula step by step. Before that let me show you what is happening behind the screen in the helper cell in K1? See that first.

When I change the start and end column numbers, the formula in cell K1 generates a text string (column IDs separated by the + arithmetic operator) which is actually the query argument used in the Query in F1.

QUERY(data, query)

Similar: How to Use Arithmetic Operators in Query in Google Sheets.

Key Formula Logic and Explanation

Assume the FROM column number in cell F2 is 2 and the TO column number in cell G2 is 5. Then the non-dynamic formula to sum column 2 to 5 using Query will be as follows.

=query({A1:F},"Select Col2+ Col3+ Col4+ Col5 label Col2+ Col3+ Col4+ Col5'Total'")

The cell K1 formula referred in my dynamic sum formula in cell F1 dynamically populates the query argument Col2+ Col3+ Col4+ Col5 for the Select and Label clause.

The label clause is used to replace the Total column header in F1 with the label “Total”.

I know the Query in cell F1 may simple to understand but not the formula in cell K1, right? The formula in cell K1 is a combination of ArrayFormula, Textjoin, Indirect, and Row.

Find that details, step by step instructions, here – How to Get Dynamic Column Reference in Google Sheets Query.

That’s all. Enjoy!