Adding Just Odd Rows or Just Even Rows

Here is a neat little trick to add just the odd rows or just the even rows in a range.

Functions used in the formula.

SUM()
IF()
MOD()
ROW()

You can find an explanation of each of the above functions on my blog.

Adding just the odd rows.

C1 contains the formula
{=SUM(IF(MOD(ROW(A1:A10);2)=1;A1:A10;0))}

Notice the braces around the formula. You must enter it as an array formula (Ctrl>Shift>Enter)




Adding just the even rows.

C1 contains the formula
{=SUM(IF(MOD(ROW(A1:A10);2)=0;A1:A10;0))}

Notice the braces around the formula. You must enter it as an array formula (Ctrl>Shift>Enter)



Notice the difference in the two formulas above. I have the differences highlighted in red.

We use a 1 in the formula to sum just the odd rows because whenever you divide an odd number by two you will get a remainder of one.

We use a 0 in the formula to sum just the even rows because whenever you divide an even number by two you will get a remainder of zero.