SUMMARY
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
A1: 1
A2: 1
A3: 1
A4: =SUM(A1:A3)
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.
back to the top
Odd Rows
Enter the following formula as an array formula in cell A4:
=SUM(IF(MOD(ROW(A1:A3),2)=1,A1:A3,0))
The formula returns a value of 2.
NOTE: To enter a formula as an array formula in Excel, press CTRL+SHIFT+ENTER.
back to the top
Even Rows
Enter the following formula as an array formula in cell A4:
=SUM(IF(MOD(ROW(A1:A3),2)=0,A1:A3,0))
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.
back to the top