Sum Values from Every X Number of Rows in Excel

Add values from every x number of rows in Excel. For instance, add together every other value in a list, or every 5th value in a list, etc. This acts as a kind of sampling technique where you are taking a subset of the total list of numbers and summing them together.

Sections:

The Formula

More Information

Notes

The Formula

=SUM(A1:A6*(MOD(ROW(A1:A6),2)=0))

Array Formula - this is an array formula and so you must enter it into the cell using Ctrl + Shift + Enter.

Result:

A1:A6 is the reference to the range of data that you want to sum. This appears twice in the formula and you should change both instances to fit your data.

2 is the interval at which you want to sum the data. 2 means to sum a value from every two rows. In this case, rows 2, 4, and 6 were added together and that's why the function returned 12. If you want to sum every 3 rows, input 3, every 5 rows, input 5, etc.

More Detailed Explanation

Array formulas are tricky, but I will try to make this explanation as plain and simple as possible.

In this formula there are two main parts:

Part 1: (MOD(ROW(A1:A6),2)=0)

This uses the MOD() and ROW() functions to find out if a row is an interval of 2 or not. (You can change the two to whichever interval you want.)

MOD() figures out the interval by returning the remainder of division based on the current row, ROW() function, and the hard-coded interval, 2, and this remainder will equal 0 at each interval.

The =0 portion checks if the remainder from MOD() equals zero or not and this check causes a TRUE or FALSE value to be generated.

Since this is an array formula, this check is made on the entire range A1:A6 and it is stored in a nice neat array, which leads to the next step.

Part 2: A1:A6

Once the function has the TRUE FALSE array from Part 1, it then multiplies that array by the range A1:A6. The FALSE values will end up evaluating to zero and only the TRUE values will remain.

The remaining values are then added together and the result is returned to Excel.

This is quite confusing if you are not comfortable with arrays, so I suggest you take a look at the next section for more information.

More Information

Here are few tutorials that will help you better understand this function and arrays in general:

MOD Function in Excel

Get the Row or Column Number of a Cell in Excel

Increment a Value Every X Number of Rows in Excel

Introducing Logic into Formulas and Functions in Excel

Multiply by True and False in Excel

Notes

This formula requires a lot more knowledge to fully understand than you actually need; honestly, just copy the formula and paste it into your worksheet and go from there. If you really want to learn more, read through the tutorials linked to in the More Information section above.

Make sure to download the sample file for this tutorial to work with this example in Excel.