Microsoft KB Archive/191008

= How to sum either odd or even rows only in Excel for Mac =

Article ID: 191008

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Excel 2004 for Mac
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q191008



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.



MORE INFORMATION
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, 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.

Additional query words: XL98 XL2004 XLX XL2001

Keywords: kbhowto KB191008

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.