Results 1 to 7 of 7
  1. #1
    Lounger
    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Lounger
    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?

  4. #4
    Star Lounger
    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

  5. #5
    Lounger
    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]

  6. #6
    Star Lounger
    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.

  7. #7
    3 Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •