How to Sum Every Alternate Column in Google Sheets [Flexible Formula]

Published on

To sum every alternate column you can use the “+” arithmetic operator in Google Sheets. It works in array too but there is a drawback. I am going to provide you with a flexible formula to sum every alternate column in Google Sheets.

Every alternate column here means every other column like A, C, D etc. or B, D, F etc. First let me show you the said arithmetic operator (plus sign) based formula.

In the below example I want to sum the alternate columns C, E, G, I in column J. Here is an array formula based on the plus operator.

Example:

sum every other column in Google Doc Sheets

Formula:

=ArrayFormula(C2:C5+E2:E5+G2:G5+I2:I5)

This formula would work exceptionally well in cell J1. There is no need for you to even drag this formula down to expand the result.

But in my point of view, this formula that sums every alternate column has a drawback. What’s that?

If the number of columns to include in the sum are more, it’s a difficult task to type all the column references. It can probably cause small typos too.

Other than this, it makes you editing the formula a complicated task. The new columns added in this range won’t be included in the formula automatically.

Here is my flexible formula to sum every alternate column in Google Sheets. It’s a combination formula, but the core functions used are MMULT and MOD.

The Flexible Formula to Sum Every Alternate Column in Google Sheets

I am using this formula in cell J2.

=ArrayFormula(mmult(if(mod(column(C1:I1),2)=1,C2:I5,0),transpose(column(C1:I1)^0)))

But to make it more flexible (the above formula is for the range C2: I5 only), I mean to use it in an infinite range C2: I, I am modifying the above formula with an additional Query.

=ArrayFormula(mmult(query(if(mod(column(C1:I1),2)=1,C2:I,0),"Select * where Col1 is not null"),transpose(column(C1:I1)^0)))

Now you should know how to use this formula in your Google Spreadsheet and how it’s going to be flexible.

Also, open this Google Sheets Functions guide in a new tab to refer the functions in use in this formula.

Sum Every Other Column – Formula Usage Explanation

We can use the last formula which is more flexible as it can include lots of rows and columns with making only a few changes.

Here are the changes that you should make to use this formula in a new range.

1. Changes to Formula When Insert New Columns:

There is no change in the formula when you insert new columns. The formula would automatically get adjusted to the new range.

But you should make sure that the other column (second column) in the newly inserted columns should contain values. It can be 0 or any numbers.

Also, you may only insert columns in multiples of 2 like 2 columns, 4 columns, 6 columns likewise.

2. Changes to Formula When Insert New Rows:

No changes required. The above formula to sum every alternate column in Google Sheets will work flawlessly in this case too.

3. Changes When Append New Rows or Columns:

The above formula is up to the column I. Suppose the last column in your data is M, do make the following changes in the formula.

Change the formulacolumn(C1:I1) tocolumn(C1:M1). Do remember! It appears twice in the formula. Also, change the rangeC2:I toC2:M

Regarding appending Rows, there are no changes required.

4. Here is the fourth and most important change that you should properly make for this formula to sum every alternate column in Google Sheets.

The range in the formula starts from column C that means the third column which is an odd number column. Take a look at this part of the formula.

if(mod(column(C1:I1),2)=1,C2:I,0)

When your data range starts from an even number column, you should change the =1 in this formula to =0.

Suppose my data range is B2: H1 and I want to sum every other column like B, D, F, and H. The formula would be like this.

=ArrayFormula(mmult(query(if(mod(column(B1:H1),2)=0,B2:H,0),"Select * where Col1 is not null"),transpose(column(B1:H1)^0)))

That’s all. Hope you have enjoyed the stay.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.