XL: How to Sum Either Odd or Even Rows Only

ID: Q136738


The information in this article applies to:
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel for Windows, versions 3.0, 4.0, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0


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 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.

Additional query words: 5.00c 5.00a 7.00a 97 XL97 XL7 XL5 XL4 XL3 every other alternate

Keywords : xlformula
Version : MACINTOSH:3.0,4.0,5.0,5.0a; WINDOWS:3.0,4.0,5.0,5.0c,7.0,7.0a,97; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbhowto


Last Reviewed: June 23, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.