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

McGimpsey & Associates 

Home

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:

  =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.