You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q213961
For a Microsoft Excel 97 and earlier version of this article, see 136738.

IN THIS TASK

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 youhave a range argument that includes either hidden rows or columns, thecells 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 twofunctions shown in this article still work as outlined.

back to the top
Properties

Article ID: 213961 - Last Review: 01/06/2006 02:52:00 - Revision: 3.1

  • Microsoft Excel 2000 Standard Edition
  • kbhowtomaster KB213961
Feedback