How can I select a range and sum every other cell?

Troy,

Do you want a formula?

This formula will sum every nth value from the mth item in the range,
assuming in this case a range of B2:B32. Just change m and n to your values
(1 and 2 in your case), and all references to the applicable range

=SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32)-ROW(B2),n)+1=m))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Troy" wrote in message
...
How can I select a range and sum every other cell?

If all that is wanted is to sum the odd-numbered rows:

=SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32),2)))

or the even-numbered rows:

=SUMPRODUCT((B2:B32)*(1-MOD(ROW(B2:B32),2)))

Of course this is less flexible than your formula, which allows one to do every 3rd or 4th row,
etc.


On Sat, 25 Oct 2003 17:11:37 +0100, "Bob Phillips" wrote:

Troy,

Do you want a formula?

This formula will sum every nth value from the mth item in the range,
assuming in this case a range of B2:B32. Just change m and n to your values
(1 and 2 in your case), and all references to the applicable range

=SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32)-ROW(B2),n)+1=m))

Troy is all over the place posting..

--

Regards,

Peo Sjoblom


"Myrna Larson" wrote in message
...
If all that is wanted is to sum the odd-numbered rows:

=SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32),2)))

or the even-numbered rows:

=SUMPRODUCT((B2:B32)*(1-MOD(ROW(B2:B32),2)))

Of course this is less flexible than your formula, which allows one to do
every 3rd or 4th row,
etc.


On Sat, 25 Oct 2003 17:11:37 +0100, "Bob Phillips"
wrote:

Troy,

Do you want a formula?

This formula will sum every nth value from the mth item in the range,
assuming in this case a range of B2:B32. Just change m and n to your
values
(1 and 2 in your case), and all references to the applicable range

=SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32)-ROW(B2),n)+1=m))

Hi Myrna,

Yes, it's one of my standards. It also allows to start at the 2nd, 3rd, 4th
in the range, not just the first.

Regards

Bob

"Myrna Larson" wrote in message
...
If all that is wanted is to sum the odd-numbered rows:

=SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32),2)))

or the even-numbered rows:

=SUMPRODUCT((B2:B32)*(1-MOD(ROW(B2:B32),2)))

Of course this is less flexible than your formula, which allows one to do
every 3rd or 4th row,
etc.


On Sat, 25 Oct 2003 17:11:37 +0100, "Bob Phillips"
wrote:

Troy,

Do you want a formula?

This formula will sum every nth value from the mth item in the range,
assuming in this case a range of B2:B32. Just change m and n to your
values
(1 and 2 in your case), and all references to the applicable range

=SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32)-ROW(B2),n)+1=m))