Excel: Sum N rows, then skip M rows

Question

I'm encountering a problem I can't seem to figure out. I have a large data set in Excel and need to (see figure):

1. sum n rows (red in figure)
2. skip m rows (black line in figure)
3. sum n rows again (blue in figure)
   etc etc The pattern is repetitive

I have searched everywhere, but I'm only able to find answers regarding summing n rows, but not the skipping rows part.

Kind regards

Answer 1

A quick solution to sum 5 rows and skip 11 in Excel 2010 would look like this:

=SUM(INDEX(B:B,(ROW()-2)*16+2):INDEX(B:B,(ROW()-2)*16+6))

but it could be made more general.

---

Here is a more general formula which shouldn't be vulnerable to insertions above row 2, assuming that N and M are defined in the Name Manager as number of rows to add and number of rows to skip:

=SUM(INDEX(B:B,(ROW()-ROW(F$2))*(M+N)+ROW(B$2)):INDEX(B:B,(ROW()-ROW(F$2))*(M+N)+ROW(B$2)+N-1))

Note that if the rows to be skipped don't contain numbers, they should be ignored by Sum anyway so this could be simplified.