Welcome to MSDN Blogs Sign in | Join | Help

Microsoft Excel

The team blog for Microsoft Excel and Excel Services.
Adding Every Other Cell in a Column

Today’s author is Bill Jelen, author of 24 books on Microsoft Office Excel. He is a regular guest on TechTV with Leo Laporte and the host of MrExcel.com, which includes more than 300,000 questions and answers about Excel.

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

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

You can also use this non-array formula

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

MrExcel provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Posted: Thursday, September 24, 2009 12:28 AM by FredK
Filed under:

Comments

Ed Ferrero said:

Perhaps somewhat easier to debug...

=SUMPRODUCT(1*ISEVEN(ROW(A1:A250)),A1:A250)

=SUMPRODUCT(1*ISODD(ROW(A1:A250)),A1:A250)

# September 23, 2009 11:21 PM

Harlan Grove said:

@Ed - except that you have to know that Excel's SUMPRODUCT doesn't automatically convert boolean TRUE/FALSE into numeric 1/0, respectively. FWIW, OpenOffice/StarOffice Calc does convert boolean values to numbers, so it can return numeric results other than 0 for

=SUMPRODUCT(ISEVEN(ROW(A1:A90));A1:A90)

and

=SUMPRODUCT(ISODD(ROW(A1:A90));A1:A90)

# September 24, 2009 2:49 AM

lhm said:

Another option to sum even/odd cells:

=SUM(NPV({0,-2},A1:A250))/2

=SUM(NPV({0,-2},,A1:A250))/2

Use --A1:A250 and array-enter in case of blanks.

# September 24, 2009 5:36 AM

Paul Seligman said:

Given that Jenny has manually selected the cells she wants to add up with CTRL, she could have simply defined a range name (Formulas-Name Manager), as these can include non contiguous cells. (the cells for the named range could also be manually entered in the name manager)

If she just needs to see the sum, selecting the range will immediately show total (and average) at bottom of sheet. Or a simple =SUM(Range_name_) formula can easily sum a range without the rather complex abstractions given above.

The advantage of this approach is that it can be used to sum any non-contiguous set of cells, not just 'every other row'. The disadvantage is that the cells do need to be manually selected the first time in most situations.

The iseven/isodd functions are easier to grasp for most people than 'MOD', as already indicated by Ed.

Why make things complicated?

# September 24, 2009 9:44 AM

T. Valko said:

Guys, what version of Excel are you using that ISEVEN/ISODD works on arrays?

Excel 2002:

=SUMPRODUCT(1*ISEVEN(ROW(A1:A250)),A1:A250) = #VALUE!

=SUMPRODUCT(--(ISEVEN(ROW(A1:A250))),A1:A250) = #VALUE!

=SUMPRODUCT(--(ISEVEN(A1:A10))) = #VALUE!

CSE =SUM(IF(ISEVEN(A1:A10),A1:A10)) = #VALUE!

# September 30, 2009 6:06 PM

Bob Phillips said:

It works on 2007, but of course Ed's formula needs something to coerce the booelans to 1/0.

# October 1, 2009 10:27 AM

Bob Phillips said:

BTW, it works on 2007 because these are ATP functions and they have been integrated into Excel, in 2003 and earlier they are part of the addin.

# October 1, 2009 10:31 AM

T. Valko said:

>It works on 2007

Ok, but why does this work:

=SUMPRODUCT(--(ISEVEN(ROW(A1:A5))),A1:A5)

But these don't work:

=SUMPRODUCT(--(ISEVEN(A1:A5)),A1:A5) = #VALUE!

=SUMPRODUCT((ISEVEN(A1:A5))*A1:A5) = #VALUE!

CSE: =SUM(IF(ISEVEN(A1:A5),A1:A5)) = #VALUE!

CSE: =SUM(--(ISEVEN(A1:A5))*A1:A5) = #VALUE!

It seems that if you use a range array as the argument it doesn't work.

This also works:

CSE: =SUM(IF(ISEVEN({1;2;3;4;5}),1))

# October 1, 2009 1:53 PM

Harlan Grove said:

Note that the formulas I gave in my previous response were from OpenOffice Calc rather than Excel.

Interesting. What do

=SUMPRODUCT(--ISEVEN(ROW(A1:A5)))

and

=SUMPRODUCT(--ISEVEN(A1:A5))

return respectively? If the first returns a number (assuming A1:A5 contains at least one number) but the second returns #VALUE!, then it'd appear ISEVEN and presumably also ISODD distinguish (wrongly! inexplicably!) between arrays and ranges. That'd imply that the bright bulbs who rewrote ISEVEN and ISODD as built-in functions rolled their own iterator rather than using the iterator used by the functions that were built into previous Excel versions.

# October 7, 2009 1:13 PM
New Comments to this post are disabled
Page view tracker