Sum every nth row of a dynamic range in Excel

For a work project, I recently found the need to sum every nth row in a variable-sized range in Excel. Here’s a simplified example worksheet to illustrate the use case:Example
Here we see three company financial metrics over a four-year period with sums of two of the metrics at the bottom. It’s likely that the number of years in the sheet will grow over time and possible that additional metrics could be added in the future. I’ve generically referred to each metric as a “property” and each year as an “item” to which those properties pertain.

The mechanics of summing the cells are conceptually straightforward: it’s the product of an array containing every value in the range and an array that bears some resemblance to a bit mask, in which every nth value is 1 and every other value is 0 (a cellmask, perhaps?). That second array can be calculated using the MOD and ROW functions:

--(MOD(ROW(OFFSET(firstCellInRange,0,0,items*properties,1))-ROW(C1),properties)=0)

In this case, C1 is the first value in the range (as in the screenshot above) and has been entered as a straightforward cell reference to allow the formula to be dragged down to sum each property. Note that the double minus casts an array of boolean values to the corresponding numeric values 0 or 1. According to an MSDN article on optimizing performance in Excel 2010, it’s slightly fast than the equivalent +0 or *1 typecasting approaches.

The bitmask-like array then simply needs to be multiplied with the values themselves using your preferred array multiplication approach:

=SUMPRODUCT(--(MOD(ROW(OFFSET(firstCellInRange,0,0,items*properties,1))-ROW(C1),properties)=0), OFFSET(firstCellInRange,0,0,items*properties,1))
{=SUM1--(MOD(ROW(OFFSET($C$1,0,0,items*properties,1-ROW(C1),properties)=0)) * OFFSET($C$1,0,0,items*properties,1))}

Performance-wise, SUMPRODUCT is typically faster than array formulas, but your mileage may vary.

References

References
1 --(MOD(ROW(OFFSET($C$1,0,0,items*properties,1

Leave a Reply

Your email address will not be published. Required fields are marked *