MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Adding Every Other Cell in a Column


January 24, 2002 - by Juan Pablo Gonzalez

Jenny asks:

Is there a quick way of adding every other cell? I have a column of about 250 cells, and I have to select them manually using Ctrl. Can you help?

Jenny, this is a matter of using a little math to remember how to determine if a number is even or is odd, and then translate that into the formula.

The formula:

=MOD(A1,2)

would return 0 if the number is even and 1 if odd. If we use instead of A1, the ROW() function, we can know if a particular row is even or odd. 

Now, suppose you're trying to calculate this formula:

=A1+A3+A5+A7+A9 ... etc.

or this one:

=SUM(A1,A3,A5,A7...)

Following formula will do the trick:

=SUM((MOD(ROW(A1:A250)-ROW(A1),2)=0)*(A1:A250))

Remember, this is a CSE Formula, to enter it you must press at the same time Control Shift Enter, instead of just Enter as regular formulas.

Note

You can also use this non-array formula

=SUMPRODUCT((MOD(ROW(A1:A250)-ROW(A1),2)=0)*(A1:A250))