I tried to use the { =SUM(ABOVE) } formula in a table but the answer was obviously incorrect

Article contributed by Suzanne S. Barnhill

 

If you have numbers in a table column and use the Table | Formula command in Word 2003 or earlier (Table Tools | Layout | Data | Formula in Word 2007), Word by default inserts the { =SUM(ABOVE) } formula field. This is quite often what you want: to sum the numbers in the column above the current cell. But often the result is obviously erroneous. There are two possible reasons for this:

  1.  If you are using Word 97 or earlier, the { =SUM(ABOVE) } field does not work for more than 85 rows. (This is not a problem with { =SUM(LEFT) } because the number of columns is limited to 63 anyway.)
  2. A much more common problem, in all Word versions, is that Word stops counting when it gets to an empty cell or one with text in it instead of a number.

To get around either of these problems, however, you can use the { =SUM( ) } field with cell addresses. For example, to add the numeric contents of the cells in column D (let's say there are 90 rows; the first one is a Heading Row, and #90 is the one where you want to put the answer), disregarding empty and nonnumeric cells, you can use the formula { =SUM(D2:D89) }.

But what if you don't know how many rows you have? Unlike WordPerfect, Word does not display cell references in the status bar. Despite many requests for this feature, it will likely never be provided: Word’s developers tell us that merged cells and nested tables make the calculation so processor-intensive that constantly updating the status bar would adversely affect Word’s performance.

There is, however, a way to get the address of a specific cell upon request. To help Word 97 users, Microsoft provided the TableCellHelper macro in the Macros8.dot template in the Macros folder of C:\Program Files\Microsoft Office\Office\. Although some subsequent Word versions have included templates containing macros, none of these has contained this macro. You can, however, download the Macros8.dot template by following this link. This macro still works in all versions, including Word 2007.

When you run this macro, a message box reports the address of the cell you're in and the total number of rows and columns in the table.

The easiest way to make this macro available in every document you create is to copy it into the Normal template, as follows:

As an alternative to copying the macro to your Normal template, you could create a new template and store it in Word's Startup directory (as defined in Tools | Options | File Locations). Storing it there makes it an add-in (or global template). You can then copy the macros to your add-in.

If you need to use the TableCellHelper macro very often, you'll want some easier way to do it than by opening the Macros dialog, selecting TableCellHelper, and clicking Run. So you may want to add it to the Table menu or a toolbar in Word 2003 or earlier or to the QAT in Word 2007. Do this as follows:

Note: When you add a macro to a menu or toolbar, by default it will display the long string of text that is displayed in the Customize dialog (and no icon). This is rarely what you want.

Word MVP Greg Maxey offers an improved version of the TableCellHelper macro at his Web site.

For another workaround, see the following article: “How to get a formula field to total an entire table column, even if some cells in the column contain text or are blank.”