This site will look better if you upgrade to a browser that supports web standards.

# SUM every Nth row or column

NOTE: Summing alternate, or every Nth row or column is one application it makes sense to me to use SUMPRODUCT only as a wrapper. Instead of using the SUMPRODUCT(array, rng) notation, using SUMPRODUCT(array * rng) multiplies the range by the array first, then passes the result to SUMPRODUCT. The largest advantage is that the range summed can be two-dimensional.

Assuming that N is a number > 0 (or a reference to range that contains such a number), and that rng is your range reference:

### Sum odd or even rows or columns

To sum every other row:

=SUMPRODUCT(MOD(ROW(rng),2)*rng) (odd rows)

=SUMPRODUCT((1-MOD(ROW(rng),2))*rng) (even rows)

To sum every other column:

=SUMPRODUCT(MOD(COLUMN(rng),2)*rng) (odd columns)

=SUMPRODUCT((1-MOD(COLUMN(rng),2))*rng) (even columns)

### Sum every Nth row or column

To sum every Nth row:

=SUMPRODUCT((MOD(ROW(INDIRECT("1:"&ROWS(rng))),N)=1)*rng)

To sum every Nth column:

=SUMPRODUCT((MOD(COLUMN(rng)-COLUMN(OFFSET(rng,,,1,1)),N)=0)*rng)

This page last updated Tuesday, 7 September 2004

© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.