sum

  • Thread starter Thread starter RichardO
  • Start date Start date
R

RichardO

Hello all:

How do I add the contents of every other column:

I have data in columns A to P. I want to add data in columns A,C,E,
e.t.c.

Thank you.


RichardO
 
Richard,

Use the following formula:
=SUMPRODUCT((MOD(COLUMN(A1:P1),2)=1)*A1:P1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Thanks a lot.

Do you mind explaining what the MOD does? You have a "2" in th
formula, does that signify that you want every other column to b
summed?, if I make that 3, will it be every third column that will b
summed? You also have an "=1" in the formula what does that do?

Thanks once again.

RichardO
 
Richard,

The MOD function returns the Modulus or remainder of one number
divided by another. So MOD(COLUMN(),2) returns the remainder of
COLUMN() divided by 2. The remainder is either a 1 or a 0. Odd
numbers have a remainder of 1 when divided by 2, and even numbers
have a remainder of 1 when divided by 2.

Thus, MOD(COLUMN(A1:P1),2) will return an array (list) of 0's and
1's depending on whether each column number in A1:P1 is even or
odd. Testing these results for = 1 returns an array of TRUE or
FALSE values, TRUE being the result when the column number is odd
and FALSE being the result when the column number if even. The
formula then multiplies this array of TRUE and FALSE values by
the values in A1:P1. Since Excel treats TRUE as 1 and FALSE as
0, and multiplying by zero equals 0, the formula creates an array
a values, each one being either 0 (if there was a FALSE in the
corresponding array element) or the value of each element in
A1:P1 (multiplied by 1, which is the equivalent of TRUE).

Finally, the SUMPRODUCT adds all this up to return the sum of
values in the odd numbered columns.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi RichardO

Re: =SUMPRODUCT((MOD(COLUMN(A1:P1),2)=1)*A1:P1)

MOD finds the remainder (modulus) after dividing the first argument by
the second argument.

By applying MOD with a second argument of 2 to the column number (A=1,
B=2...) we get a remainder of 1 for all odd numbered columns.

MOD(COLUMN(A1:P1),2)=1 will return TRUE for all of the ODD numbered
columns and FALSE for all even numbered columns (where the remainder
will be 0).

This gives us a series of multiplications:

TRUE*A1
FALSE*B1
TRUE*C1
FALSE*D1

When you multiply a Boolean operator it is coerced to 1 for TRUE and 0
for FALSE.

SUMPRODUCT adds up the results of the multiplications so in this case
only cases where TRUE applies will cause the amount in the column to
be added

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top