# How do I sum all even or odd columns in Google Spreadsheets?

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?

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)

## Example

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

Significantly shorter formula than accepted answer:

``````=SUM(FILTER(C1:F10, ISODD(COLUMN(C1:F10))))
``````
• 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. 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. 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). Nov 29 '19 at 18:01

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

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