SUM Non-Contiguous Ranges of a Column – SUM EACH block of consecutive cells, starting from specific position, & skip specific number of cells
SUM EACH of the Non-Contiguous Blocks of consecutive number of cells, with a specified start position, after skipping specific number of consecutive cells
Cell D2 formula: In consecutive cells of column D, SUM specified number (cell B3 value) of consecutive column A cells, after skipping specified number of cells / rows (cell B4 value), & starting from a specified Position (cell B2 value).
———————————————————————————————–
Cell D2 formula: In consecutive cells of column D, SUM every 4 (cell B3 value) consecutive column A cells, after skipping every 2 (cell B4 value) cells / rows, & starting from cell A4 ie. 3rd Position (cell B2 value).
Starting from a specified Position (cell B2 value), of a column (column A): For example, in Range A2:A100 the 3rd position (entered as 3 in cell B2) will be cell A4, the 5th position (entered as 5 in cell B2) will be A6; in Range A5:A120 the 2nd position will be cell A6 and the 5th position will be cell A9.
Cell D2 formula uses the volatile OFFSET function. Another formula is given in cell E2 which avoids using the volatile OFFSET function.
Cell D2 formula:
=SUM(OFFSET( A$2, (ROW(A1)-1)*( B$3+B$4)+ B$2-1, 0, B$3))
Explanation – Cell D2 formula (Non-Array Formula):
1) (ROW(A1) -1)*( B$3+ B$4)+ B$2-1: returns the number of rows to offset from cell A2 ie. position of cell where to start SUM for each cell formula (start position of each block of cells). In cell D2 it returns 2 (offset by 2 means start cell is the 3rd cell of A4), in cell D3 it returns 8 (offset by 8 means start cell is the 9th cell of A10), in cell D4 it returns 14, in cell D6 it returns 20, and so on. “B$2-1” accounts for the Start Position so if B2 =3 then “B2-1” returns 2 by which cell A2 is Offset & this returns A4 ie. 3rd position in the range A2:A100. Copying to successive rows will convert ROW(A1) to ROW(A2) ie. from 1 to 2 and so on. “B$3+B$4” adds the number of cells to SUM and cells to Skip. Cell D2: For cell D2 (1st formula cell), “ROW(A1)-1” returns zero & multiplying this with (B$3+ B$4) returns zero for D2, and then added with “B$2-1” which returns the position (ie. 2) where to start SUM in cell D2. Cell D3: For cell D3 (2nd formula cell) “B$3+B$4” is multiplied by 1 ie. “ROW(A2)-1”, and then added with “B$2-1” which returns the position (ie. 8) where to start SUM in cell D3. And so on. This is the rows argument of the OFFSET function.
2) Cell B3 value is used as for height argument of Offset function, which specifies the number of consecutive cells to be included in SUM.
3) The OFFSET function is used with the rows argument in 1) above which returns the the start position of each block of cells ie. position of cell where to start SUM for each cell formula, and with the height argument in 2) above which returns the number of cells in each block to be included in SUM.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
Cell E2 formula: This Cell E2 formula performs the same as cell D2 formula, but avoids using the volatile OFFSET function which is used in cell D2. In consecutive cells of column D, SUM every 4 (cell B3 value) consecutive column A cells, after skipping every 2 (cell B4 value) cells / rows, & starting from cell A4 ie. 3rd Position (cell B2 value). Enter formula in cell E2 (or any other start cell), & copy down – Normal, Non-Array formula.