# How to Sum Either Odd or Even Rows Only

Article translations
Close
Expand all | Collapse all

## SUMMARY

When you use any of the worksheet functions in Microsoft 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 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 would like to sum only the Odd or the Even rows in a range, instead of trying to hide rows, which does not work in this case, you can use the following formulas.

### Odd Rows

```   =SUM(IF(MOD(ROW(A1:A3),2)=1,A1:A3,0))
```
If you enter this as an array formula in cell A4 in the above example, it returns a value of 2.

NOTE: To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.

### Even Rows

```   =SUM(IF(MOD(ROW(A1:A3),2)=0,A1:A3,0))
```
If you enter this as an array formula in cell A4 in the above example, it returns a value of 1.

NOTE: If you still want to hide rows for the above example, the two functions given will still work as outlined.

## Properties

Article ID: 136738 - Last Review: August 17, 2005 - Revision: 1.3
##### APPLIES TO
• Microsoft Excel 97 Standard Edition
• Microsoft Excel 95 Standard Edition
• Microsoft Excel 5.0 Standard Edition
##### Keywords:
kbhowto KB136738
Retired KB Content Disclaimer