10

The following is given as a solution for Excel:

=SUM(IF(MOD(COLUMN(B16:AO16),2)=0,B16:AO16,0))

=0 changes to =1 to toggle between odd and even columns. However, this doesn't work in Google Spreadsheets - unless the output of MOD goes to an array function, MOD just calculates against the first value in the range.

How can I do this in Google Spreadsheets?

11

I would use the following formula.

Formula

=SUM(ARRAYFORMULA(IF(ISODD(COLUMN(C1:F10))=TRUE,C1:F10)))

Explained

The ISODD function will check if the column number is odd (TRUE). The IF function will return the number if TRUE, else FALSE. The ARRAYFORMULA does that for the complete range and the sum will simply sum up the numbers (and ignore the boolean results)

Screenshot

enter image description here

Example

I've created an example file for you: sum odd or even column ranges

0
3

Significantly shorter formula than accepted answer:

=SUM(FILTER(C1:F10, ISODD(COLUMN(C1:F10))))
4
  • Unfortunately, this yields "unexpected column count" on the second argument. Sure enough, the ISODD(...) function appears to simply give a single value for the range. – Paul Lammertsma Oct 15 '15 at 12:35
  • Could you post an example? It is working for me for both even and odd column counts. – saji Oct 16 '15 at 22:20
  • I don't have an example at hand at the moment, but I was selecting a single column range. – Paul Lammertsma Oct 16 '15 at 23:22
  • Thanks! This one worked for me to sum values in every 3rd column, by changing ISODD to MOD(<range>, 3)=0, like so: =SUM(FILTER(B3:F5, MOD(COLUMN(B3:F5),3)=0)). I couldn't get the same effect by adapting the ARRAYFORMULA approach of the accepted answer (although it's probably possible). – waldyrious Nov 29 '19 at 18:01
2

Even though this has an accepted answer, I wanted to find a non-array version of this for two reasons:
1. I wanted a version that worked in Excel (2016, at least) as well, and 2. I wanted to know if I could

I came up with this:

=SUMPRODUCT($A$2:$A$20,--(MOD(ROW($A$2:$A$20),2)=0))

If your numbers are in A2:A20, the above will sum the even-row numbers. change the last 0 to a 1 for Odd-row numbers.

Here are links to working examples in Google Sheets and Excel Online

1
  • Nice! I like that this one can be adapted to work for any interval size, by replacing the second argument of the MOD function :) – waldyrious Nov 29 '19 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.