SUM every other row in a column

[worzell]

Hello,

I am relatively new to excel and I got the following formula from
Google groups to sum every other row in a column. I have used it and
it works but out of curiosity could some one please break it down for
me so that I also UNDERSTAND it???!!! Also I believe it is an array
formula i.e. you have to press ctrl+shift+enter but what exactly soes
that mean?!

OK the formula is:

=SUM((MOD(ROW(A1:A20),2)=0)*A1:A20)

Many Thanks to anyone who can help.

Worzell.

 

[Bernie Deitrick]

Anthony,

Your formula

=SUM((MOD(ROW(A1:A20),2)=0)*A1:A20)

The ROW(A1:A20) part expands to
=SUM((MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},2)=0)*A1:A20)

The MOD(ROW(A1:A20),2) part returns the remainder when you divide by 2
=SUM(({1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0}=0)*A1:A20)

The (MOD(ROW(A1:A20),2)=0 part returns TRUE or FALSE:
=SUM({FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;
FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}*A1:A20)

FALSE times a value = 0, TRUE times a value = Value: This example just has 1
to 20 in cells A1:A20, so you end up getting:
=SUM({0;2;0;4;0;6;0;8;0;10;0;12;0;14;0;16;0;18;0;20})

Then your SUM simply sums the numbers.

HTH,
Bernie
MS Excel MVP

 

[Peo Sjoblom]

To add what Bernie said, you can replace SUM with SUMPRODUCT and you don't
have to array enter it

=SUMPRODUCT(--(MOD(ROW(A1:A20),2)=0),A1:A20)

entered normally and it won't choke if you have a text value in range
A1:A20,
which the first formula will do

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Harlan Grove]

To add what Bernie said, you can replace SUM with SUMPRODUCT and you don't
have to array enter it

=SUMPRODUCT(--(MOD(ROW(A1:A20),2)=0),A1:A20)

entered normally and it won't choke if you have a text value in range
A1:A20, which the first formula will do

...

This could be simplified further,

=SUMPRODUCT(1-MOD(ROW(A1:A20),2),A1:A20)

to sum even-numbered rows, and

=SUMPRODUCT(MOD(ROW(A1:A20),2),A1:A20)

to sum odd-numbered rows.

 

[worzell]

Bernie,

That's great, many thanks!!!

Anthony

Anthony,

Your formula

=SUM((MOD(ROW(A1:A20),2)=0)*A1:A20)

The ROW(A1:A20) part expands to
=SUM((MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},2)=0)*A1:A20)

The MOD(ROW(A1:A20),2) part returns the remainder when you divide by 2
=SUM(({1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0}=0)*A1:A20)

The (MOD(ROW(A1:A20),2)=0 part returns TRUE or FALSE:
=SUM({FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;
FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}*A1:A20)

FALSE times a value = 0, TRUE times a value = Value: This example just has 1
to 20 in cells A1:A20, so you end up getting:
=SUM({0;2;0;4;0;6;0;8;0;10;0;12;0;14;0;16;0;18;0;20})

Then your SUM simply sums the numbers.

HTH,
Bernie
MS Excel MVP