SUM every other row in a column

  • Thread starter Thread starter worzell
  • Start date Start date
W

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.
 
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
 
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)
 
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
Click to expand...
...

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.
 
Bernie,

That's great, many thanks!!!

Anthony

Bernie Deitrick said:
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
Click to expand...
 
Back
Top