# Thread: Summing Every Other Column/Row (2000)

1. ## Summing Every Other Column/Row (2000)

I have a very large worksheet (100 Rows By 1000 Columns). In every other column (say A, C, E, etc.) I have data in numerical format (i. e., accounting with no decimal places or dollar signs). In adjacent columns (i. e., B, D, F, etc.) I have data in percentage format. What I would like to do is sum all the non-percentage cells in a row in the first empty cell on the far right, and in the cell next to it, all the % cells. Any ideas for a shortcut?

2. ## Re: Summing Every Other Column/Row (2000)

To sum columns A, C, E etc in row 1:

=SUMPRODUCT(MOD(COLUMN(\$A1:\$CV1),2),\$A1:\$CV1)

and to sum columns B, D, F etc. in row 1:

=SUMPRODUCT(1-MOD(COLUMN(\$A1:\$CV1),2),\$A1:\$CV1)

CV is column 100, adjust as needed.

3. ## Re: Summing Every Other Column/Row (2000)

Thanks, Hans. As uszual, it works perfectly. By the way, would you mind explaining how it works?
Thanks again

4. ## Re: Summing Every Other Column/Row (2000)

COLUMN(\$A1:\$CV1) returns an array of column numbers: 1, 2, 3, 4, ..., 99, 100
MOD(COLUMN(\$A1:\$CV1),2) computer the reminders of these column numbers after division by 2: 1, 0, 1, 0, ...., 1, 0
SUMPRODUCT multiplies these 1s and 0s with the values of the cells in a row (row 1 in the example). The values from odd-numbered columns are multiplied by 1 and hence included, and the values from even-numbered colums are multiplied by 0 and hence discarded. Finally, the results are added.

For the other formula, we use 1-MOD(COLUMN(\$A1:\$CV1),2). This results in 0, 1, 0, 1, ..., 0, 1. In other words, the 1s and 0s are switched.

#### Posting Permissions

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