Back to Excel Tips Index

SUM EVERY OTHER ROW

Let's say in cells B27 thru E27 (see figure below) I want to sum year to date for year 2006. If we are in August of 2007, I want to only sum the months from January 2006 to August 2006 so that I have an accurate comparison of year over year sales.

If I were to enter a formula in to B27 that stated, =SUM(B2:B25) I would sum together all of 2006 as well as year to date 2007!  That's no good.

I could enter the formula, =SUM(B3,B5,B7,B9,B11,B13,B15,B17), but then each month I have to go in and manually update it. To me, that's tedious.

A third option is to enter a dynamic formula that will sum every other row but only up to the current month, which is exactly what the following does.

In cell B27 of the example below:

=SUM(IF(MOD(ROW(B$2:INDEX(B$2:B$25,MONTH(TODAY())*2)),2)=0,B$2:INDEX(B$2:B$25,MONTH(TODAY())*2),""))

Note: This is an array formula and must be confirmed with Ctrl+Shift+Enter, not just Enter.

This can then be copied to cells C27:E27.

Back to Excel Tips Index