Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    London, Gtr London, England
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tables and sum above (2003)

    I have a table in Word 2003 with two rows. Row one has 12 cells and Row two has 5 cells. The last three cells of Row one line up with the last three cells in Row 2. I have an amount in Row One, Cell 12 which I would like to sum in Row two cell 5. I put in Sum Above in Row Two cell 5 but it not only sums above but also sums any other numbers in Row one from cell 5 onwards. For example
    Row One Cell 5 contains a date - 24-Mar-05
    Row One Cell 7 contains text and a number - Staff: 1
    Row One Cell 12 contains an amount - 100.00
    Sum above in Row Two cell 5 adds 24 + 05 + 1 + 100 which equals 130!

    It seems to work in Word 2000 ok, but in 2003 it don't like it. Any ideas anyone?
    Thanks

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

    Re: Tables and sum above (2003)

    Why do you need a SUM formula if you want to refer to the amount in a single cell? I don't know why SUM doesn't work correctly, but try the following:
    - Remove the formula from the cell in row 2, column 5.
    - Press Ctrl+F9 (in this cell) to insert field brackets { } (do not type the brackets yourself).
    - Type =R1C12 between the brackets (or =L1).
    - Press F9 to hide the field code and update the value.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    4,981
    Thanks
    2
    Thanked 398 Times in 328 Posts

    Re: Tables and sum above (2003)

    Hi Lynn,

    Word's SUM(ABOVE) formula works on values that are in the same column of the table. The mere fact that two cells are aligned vertically doesn't mean that Word's SUM(ABOVE) formula will view them as being in the same column, since column positioning is counted from the left side of the table. Therefore, a SUM(ABOVE) formula in column 5 will add up all column 5 values, subject to the limitations explained in <post#=365442>post 365442</post#>. As you've discovered, Word even tries to interpret dates as numbers when a referenced cell contains a date, with weird results. This behaviour apparently afflicts all Word versions, so I'm surprised you found a difference.

    As for the 130 result in row 2, it sounds like row 1 has multiple values in the same cell as the date, perhaps separated by tabs, rather than the values being in separate columns. When more than one number appears in a cell, Word's table formulae add them all together.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    New Lounger
    Join Date
    May 2002
    Location
    London, Gtr London, England
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tables and sum above (2003)

    Thanks for this. It is weird that there is a difference between 2000 and 2003. The template I have created in 2000 is an expenses template, the table with the figures and dates in it, gets row after row added, when the user clicks on a finished button, the two tables get joined together and then the cell 5 of the row with 5 cells in it uses the sum above to calculate the total amount. It all works well in 2000 and has done so for the last three years! we are soon to move to 2003 and while one of our users on the pilot tried to create an expense doc using this template, thats when we discovered that the sum above does not actually some above. Thanks anyway - looks like I am going to have to rewrite this template.

  5. #5
    New Lounger
    Join Date
    May 2002
    Location
    London, Gtr London, England
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tables and sum above (2003)

    Thanks for this - will give it a try. The reason I need a sum formula if I refer to the amount in a single cell, is that the template is an Expenses template, so the user may only have one expense entry, and the Total cell will still need to calculate for the accounts department.
    Lynn

  6. #6
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tables and sum above (2003)

    Hi Lynn
    I had better success with my expense forms when I changed my word table to an embedded excel table in the word template. Users were comfortable with the table structure and excel handled the calculations well. Accounting was thrilled because they could just grab the sheet and run their weekly reports from it. Another option is to use a protected form with calculations on exit. Both have worked well for me.
    Good luck!

Posting Permissions

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