HOW TO: Use SUM Function on Only Odd or Even Rows in Excel 2000

This step-by-step article explains how to use the SUM function on only the odd rows or the even rows in a range.

When you use any of the worksheet functions in Excel, and you have a range argument that includes either hidden rows or columns, the cells in these hidden areas are still used by the function. For example, if you have the following data in a worksheet the SUM function in cell A4 returns a value of 3. If you then hide Row 2, the SUM function still returns a value of 3.

If you want to use the SUM function on only the odd rows or the even rows in a range, you can use the following formulas.

Odd Rows

Enter the following formula as an array formula in cell A4: The formula returns a value of 2.

NOTE: To enter a formula as an array formula in Excel, press CTRL+SHIFT+ENTER.

Even Rows

Enter the following formula as an array formula in cell A4: The formula returns a value of 1.

NOTE: If you want to hide rows for the above example, the two functions shown in this article still work as outlined.