# Thread: Sum every other column (Excel 2000)

1. ## 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. ## 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. ## 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. ## 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. ## Re: Sum every other column (Excel 2000)

Perfect.... Thanks for the help. [img]/forums/images/smilies/smile.gif[/img]

6. ## 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. ## 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))

#### Posting Permissions

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