Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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?
    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •