formula to add up EVERY OTHER cell in a row

[Task Lead Nicole]

Can I set up a formula in an xls to add up EVERY OTHER cell in a row? And how
long can a string in a formula be? (are there limitations?)

I'm getting an error when I manually type in the list of cells to add up.

 

[Chip Pearson]

Try the following formula. Change the cell range as desired.

=SUM(A1:A10*MOD(A1:A10,2))

A cell can contain up to 32K characters, although only about 1000
will display unless some trickery is used.


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




"Task Lead Nicole" <[email protected]>
wrote in message
news:[email protected]...

 

[Nicole L.]

Can you explain what that formula means?
Sum of cells A1 thru A10, then what?

Thank you!

 

[Nicole L.]

So, using your formula, with my range, here's what I came up with and Excel
yelled at me -- it's not generating the correct result.

=SUM(E8I8*MOD(E8I8,2))

Do I need different parentheses or something?

 

[Peo Sjoblom]

One way normally entered

=SUMPRODUCT(--(MOD(COLUMN(E8I8),2)=1),E8I8)

will sum E8, G8, I8 and so on


Regards,

Peo Sjoblom

 

[Nicole L.]

So far, that has worked - Thanks!
But what do the double-hyphens mean?
Can you translate the whole string for me, verbatim?
I just like to know, so I can tweak in future for future reference, ya know.

Thanks again.

 

[Aladin Akyurek]

To sum every 2nd number in a vertical range...

=SUMPRODUCT(--(MOD(ROW(A3:A200)-CELL("Row",A3)+0,2)=0),A3:A200)

To sum every 2nd number in a horizontal range...

=SUMPRODUCT(--(MOD(COLUMN(A3:Z3)-CELL("Col",A3)+0,2)=0),A3:Z3)

 

[Bob Phillips]

Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

Nicole,

I should have added that this is an array formula. When you type
in the formula, you must press CTRL+SHIFT+ENTER rather than just
Enter. If you do this properly, Excel will display the formula
enclosed in curly braces.

The formula works by creating two array. The first is simply A1,
A2, A3, ... A10. The second is the result of MOD(A1,2), MOD(A2,
2), MOD(A3,2)...MOD(A10,2). The MOD function with an argument of
2 will return either a 0 if the number is even or 1 if the number
is odd. Multiplying these two arrays together (multiplying each
argument in the first array by the corresponding element in the
second array) returns an array like A1*1, A2*0, A3*1,...A10*0.
The SUM function simply sums these elements.


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