General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

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:


To sum every Nth column:


Valid XHTML 1.1Valid CSSMade on a Macintosh