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.
