This site will look better if you upgrade to a browser that supports web standards.
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:
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)
To sum every Nth row:
To sum every Nth column:
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.