Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am new to superuser. Kindly pardon if I make some mistakes.

I am working with tables having numerical data in Word (no not even embedded Excel tables - don't ask why). It so happens that many tables have headers/sub headers with numerical values too. When I use formula "=SUM(ABOVE)" the expected total shouldn't pick the header value. But it does !

See the following sample. "Total (E)" represents what I expect and "Total (A)" is what I see.

|----------|---------------|-------|
| Header A |    Header B   | Total |
|          |---------------|       |
|          | 1 | 2 | 3 | 4 |       |
|----------|---------------|-------|
| One      | 4 | 3 | 2 | 1 | 10    |
|----------|---|---|---|---|-------|
| Two      | 0 | 0 | 1 | 0 | 1     |
|----------|---|---|---|---|-------|
| Three    | 0 | 1 | 0 | 0 | 1     |
|----------|---|---|---|---|-------|
| Total (E)| 4 | 4 | 3 | 1 | 12    |
|----------|---|---|---|---|-------|

|----------|---|---|---|---|-------|
| Total (A)| 5 | 6 | 6 | 5 | 22    |
|----------|---|---|---|---|-------|

I know I can correct the right bottom total by changing from "=SUM(LEFT)" to "=SUM(ABOVE)" But my real question is: How does one make Word omit the header values when picked up other totals ? I read somewhere that we can specify ranges such as "=SUM(b3:b5)". However, my tables are many in number and vary in terms of number of rows & columns. It would be a lot of manual effort to edit all those formulae. Is there some trick such as using "=SUM(b3:bN)" (literal N) so that it picks up everything under b2 to calculate the sum ?

If version matters, I'm trying this on Word 2010. But my document (docx) will be modified using Word 2007 too. Thanks in advance! Parag Doke

share|improve this question
    
I have posted the same question on the MS Forums. Here is the link: answers.microsoft.com/en-us/office/forum/office_2007-word/…. –  Parag Doke May 28 '12 at 8:01
add comment

1 Answer 1

up vote 0 down vote accepted

Here are three quick and dirty solutions:

  1. Change the numbers below Header B to words: One, Two, Three, Four.

    |----------|--------------------------|-------|
    | Header A |         Header B         | Total |
    |          |--------------------------|       |
    |          | One | Two | Three | Four |       |
    
  2. Add a text character either before or after the numbers below Header B: either ~4 or 4~. Change the color of the character to white (or the color of the background) so that it can't be seen.

    |----------|-------------------|-------|
    | Header A |      Header B     | Total |
    |          |-------------------|       |
    |          | ~1 | ~2 | ~3 | ~4 |       |
    

    or

    |----------|-------------------|-------|
    | Header A |      Header B     | Total |
    |          |-------------------|       |
    |          | 1~ | 2~ | 3~ | 4~ |       |
    
  3. Insert a blank row between the header numbers and the rest of the numbers. You can then edit the borders, change the font size, etc. to make it look better.

    |----------|---------------|-------|
    | Header A |    Header B   | Total |
    |          |---------------|       |
    |          | 1 | 2 | 3 | 4 |       |
    |----------|---------------|-------|
    |                                  |
    |----------------------------------|
    | One      | 4 | 3 | 2 | 1 | 10    |
    |----------|---|---|---|---|-------|
    
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.