Results 1 to 7 of 7

20030912, 13:26 #1
 Join Date
 Sep 2003
 Location
 Camp Springs, Kentucky, USA
 Posts
 36
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sum every other column (Excel 2000)
I have a spreadsheet that has data grouped in sets of two columns each.
For example,
client "Bob" has a number in column D and a percent in column E.
client "Joe" has a number in column F and a percent in column G.
client "Ray" has a number in column H and a percent in column I.
I want a sum of D, F & H without E, G & I.
I realize that I can just enter "=D3+F3+H3", but this gets very tedious as I enter more columns and have to add the new cells to the formula manually.
Is there a way to enter a formula that will sum every other cell?

20030912, 13:37 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sum every other column (Excel 2000)
If your percents are all <100% and NONE of the other columns are <1 you could use:
=Sumif(d3:I3,">=1")
Steve

20030912, 14:04 #3
 Join Date
 Sep 2003
 Location
 Camp Springs, Kentucky, USA
 Posts
 36
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum every other column (Excel 2000)
Steve,
Thanks, but that won't do the trick. I need something more generic as I have the same situation where both cells are whole numbers (not percentages). Is it possible to do a "sumif" and use the formatting of the cell (like the background color) to check if it should be added?

20030912, 14:12 #4
 Join Date
 Jun 2001
 Posts
 76
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum every other column (Excel 2000)
You can try an array formula. Assuming data is in the EVEN numbered columns in the range of A through F (and on Row 3) here is an example
{=SUM(IF(MOD(COLUMN(A3:F3),2)=0,A3:F3))}
enter the formula without the brackets and press SHIFT+CTRL+ENTER for Excel to accept as an array formula
Change the range to whatever fits your circumstance, but be sure the range is the exact same in both places in the formula. Also  you can change the if condition to "=1" instead of "=0" if the data you need to sum is on the ODD numbered column.
Hope this helps,
Bill

20030912, 14:17 #5
 Join Date
 Sep 2003
 Location
 Camp Springs, Kentucky, USA
 Posts
 36
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum every other column (Excel 2000)
Perfect.... Thanks for the help. [img]/forums/images/smilies/smile.gif[/img]

20030912, 14:27 #6
 Join Date
 Jun 2001
 Posts
 76
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum every other column (Excel 2000)
Glad it helped  btw variations on this method can be used to sum any Nth numbered column (or row). Good luck.

20030912, 20:05 #7
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum every other column (Excel 2000)
=SUMPRODUCT((MOD(COLUMN($D$3:$I$3)CELL("Col",$D$3:$I$3)+0,2)=0)*($D$3:$I$3))
Microsoft MVP  Excel