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:
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.
