Adding Every Fifth Value in a Column in Excel

 Last reviewed: November 2, 1994
Article ID: Q45559
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0

SUMMARY

The following function sums every fifth value from a column of values: 

   {=SUM(IF(MOD(ROW(Values),5)=x,Values,0))}
The above is an array formula, so press SHIFT+CTRL+ENTER to enter it. Do not type the braces { } yourself; these indicate an array formula.

In the above formula, "Values" is the columnar information (for example, D6:D55), and "x" is a value that indicates which set to add, as in the following examples: 

   1 = Row #s 1, 6, 11, 16, 21, etc....
   2 = Row #s 2, 7, 12, 17, 22, etc....
   3 = Row #s 3, 8, 13, 18, 23, etc....
   4 = Row #s 4, 9, 14, 19, 24, etc....
   0 = Row #s 5, 10, 15, 20, 25, etc....
Values for x other than 0-4 result in a 0 (zero) result. In the above example, D6:D55, x=3 adds every fifth entry in the column beginning at row 8, or D8. To add every fourth row, change 5 to 4, and limit your values for x to 0 to 3. Likewise, to add every sixth row, change 5 to 6 and limit your values for x to 0 to 5.

MORE INFORMATION

The formula uses the value of the row coordinate of each cell in the range Values to determine whether or not the value contained in the cell should be SUMmed. If the MOD of the row and the second value of the argument (in this case, 5, because every fifth row is being added) equal the value for x in the formula, then that cell's value is added to the result of the formula; otherwise, a 0 (zero) is returned for that particular cell. For example: 

   {=SUM(IF(MOD(ROW(A1:A15),5)=0,A1:A15,0))}
The above formula takes the row value of each cell from A1 to A15, divides it by 5 (the function of MOD), and compares the remainder (the result of a MOD function) to 2. If the remainder is 2, then the value of the cell is added to the overall result. In this case, the values in cells A2 (2/5 = 0r2), A7 (7/5 = 1r2), and A12 (12/5 = 2r2) are returned (note that all have a remainder of 2); all other cells in the range return a value of 0 (zero).

By using the COLUMN() function in place of ROW(...), values in every nth value in a row could be added (or otherwise manipulated) in a similar fashion.

Note that the range in the formula may begin at any cell, with any size range.

KBCategory: kbusage
KBSubcategory:

Additional words: 5.00 2.0 2.00 2.01 2.1 2.10 3.0 3.00 4.0 4.00
4.0a 4.00a

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.