Erroneous Table Math

by Allen Wyatt
(last updated June 6, 2015)

You may have noticed that if you do table math in a Word table, some of your answers may not be exactly what you would expect. For instance, if you are using the =SUM(ABOVE) function in a table cell to add together the values in a table, the function will add any numeric value in any cell. Thus, if your headings have text in them, such as Quarter 1, Quarter 2, and so on, the function will pull the values 1 and 2 out of the cells and add them into your total.

There are several ways around this problem. The first is to use a specific range in the SUM function instead of the ABOVE attribute. The only drawback is if you edit the table, it could throw your SUM calculations off. Thus, if you add or delete rows from your table on a regular basis, you would need to check the SUM range over and over again to make sure it is always working on the proper range of cells.

A related approach is to make your summation field just a bit more complex—just subtract the first row from the sum. For instance, you could use the following field in the cell where you want the total to appear:

{ =SUM(ABOVE) – A1 }

This formulaic approach is a simple way to make sure that you compensate for the known "glitch" in how Word applies the ABOVE attribute.

Another option is to spell out the numbers in the cells you don't want added in. This would require using headings such as Quarter One and Quarter Two, rather than Quarter 1 and Quarter 2. You could also use Roman numerals, such as Quarter I and Quarter II. For many purposes, the approaches might not be acceptable.

Still another workaround is to insert a blank row between your headings and the body of your table. The =SUM(ABOVE) function stops adding when it reaches a blank row. If you don't want the appearance of a blank row, then you can always format the row height to be a very small value, such as a single point. (One point is the smallest value that you can use for a row height.) If the table borders are off, a row this small will be all but invisible.

A variation on this approach is to split your table into two: the first table would be just your headings, while the body would be in the next table. Then, either format the intervening paragraph so it is very small (again, 1 point is nice) or simply format the paragraph as hidden text and make sure hidden text doesn't print on your printout. The result is that the two tables print so close to each other that they look like one. The drawback is that if you need to subsequently change table column width, you must do it for each of the tables independently.

Perhaps one of the most interesting workarounds is to replace the space just before the number in the heading with a different symbol that looks like a space, but which causes Word to ignore the numbers following it. Try the following:

  1. Select the space in the heading, just before the quarter number.
  2. Choose Symbol from the Insert menu. Word displays the Symbol dialog box.
  3. Make sure the Symbol font is selected.
  4. Double-click on the very first character in the font table. It looks like a blank. Word replaces the space you selected in step 1 with the character.
  5. Click on Close.
  6. Recalculate the totals in the column.

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (1398) applies to Microsoft Word 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Converting Many DOC Files to DOCX

Need to convert a bunch of old DOC files to the newer DOCX format? Word doesn't provide the capability to convert a bunch of ...

Discover More

Using a Week Number as One Criterion in a Formula

The SUMIFS function can be quite powerful in conditionally summing information based on criteria you specify. This tip looks ...

Discover More

Ensuring Conditional Formatting and Data Validation is Copied

If you use an Excel worksheet for entering data (a quite common task, actually), then you need to be concerned with how ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

MORE WORDTIPS (MENU)

Different Table Captions on Multiple Pages

If you have a table that spans multiple pages, you may want to add a caption to the table and have that caption use different ...

Discover More

Footnotes for Tables

Word includes a powerful feature that allows you to add footnotes and endnotes to your document. What if you want them at the ...

Discover More

Moving a Table Column

Want to move a column in a table very easily? You can do so by using the same editing techniques you are already using.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three minus 1?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Word that uses the menu interface (Word 97, Word 2000, Word 2002, or Word 2003)? This site is for you! If you use a later version of Word, visit our WordTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing