Sum even columns

[CB]

I am using MS Vista and Excel 2007. My question is how do I sum even columns
in a spreadsheet? My spreadsheet is set up in the following manner:

11/7/08 11/14/08 11/24/08
Col A1 Col B1 Col C1 Col D1 Col E1 Col F1 Col
G1...etc
Sold to 20 $20.00 10 $10.00 5 $5.00

In column B1, D1 and F1 I have a number and in column C1, E1 and G1 I have
an associated cost. The spreadsheet can require expansion to the right to
column EZ. My question is how do I sum Column B1, D1...EZ1 in a
predetermined cell (FA1)? I cannont use the sum function as it has a limit
on the number of individually selected cells that can be summed. Is there a
way to tell it to sum starting at B1 every other cell to the right stopping
at EZ1?

Thanks in advance for your help.

 

[Gary''s Student]

=SUMPRODUCT(--(MOD(COLUMN(B1:EZ1),2)=0),B1:EZ1)

 

[JE McGimpsey]

One way:

=SUMPRODUCT(1:1,--(MOD(COLUMN(1:1),2)=0))

 

[eliano]

I am using MS Vista and Excel 2007.  My question is how do I sum even columns
in a spreadsheet?  My spreadsheet is set up in the following manner:

                  11/7/08                11/14/08            11/24/08  
Col A1        Col B1      Col C1    Col D1   Col E1    Col F1       Col
G1...etc
Sold to        20            $20.00    10       $10.00    5            $5.00

In column B1, D1 and F1 I have a number and in column C1, E1 and G1 I have
an associated cost.  The spreadsheet can require expansion to the rightto
column EZ.  My question is how do I sum Column B1, D1...EZ1 in a
predetermined cell (FA1)?  I cannont use the sum function as it has a limit
on the number of individually selected cells that can be summed.  Is there a
way to tell it to sum starting at B1 every other cell to the right stopping
at EZ1?

Thanks in advance for your help.

Hi CB.
Try also:

Public Sub prova()
Dim c As Long
Dim r As Long
Dim totale As Long
For r = 2 To 100
totale = 0
If Cells(r, 1).Value = "" Then Exit Sub
For c = 1 To 156 Step 2
totale = totale + Cells(r, c).Value
Next
Cells(r, c + 1).Value = totale
Next
End Sub

Regards
Eliano

 

[ryguy7272]

When I fill down, I get Gary"s function to work, but not the one posted by JE
McGimpsey. Maybe it should be something like this:
=SUMPRODUCT(A1:Z1,--(MOD(COLUMN(A1:Z1),2)=0))
Then, when you fill down, it seems to work.

Ryan---

 

[JE McGimpsey]

Hmmm... the formula posted works fine here when copied down. What
"didn't work" for you?

 

[ryguy7272]

False alarm. Sorry, JE McGimpsey, your function didn't seem to work on my
office laptop, but when I tried it on my home laptop it worked just fine.
Maybe some setting on my version of Excel in the office... I know
Auto-Calculation was turned on. Not sure what happened. Sorry 'bout that!


Ryan--