Adding Every Fifth Value in a Column in Excel

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.

Additional query words:

Keywords :
Version :
Platform :
Issue type :