How to get a formula field to total an entire table column, even if some cells in the column contain text or are blank

Article contributed by Dave Rado

The big problem with using the { =SUM(ABOVE) } field in Word (by selecting Table + Formula) to sum a table column, is that if there are any blank cells in the column, or any cells containing text, the formula only sums the cells below the first non-numeric cell it encounters. Another problem is that it can only total a maximum of 85 cells.

One workaround is to specify the row and column numbers in the formula; for example, to sum rows 1-20 of the fourth column (column D), you could use { =SUM(D1:D20) }. The big problem with that, though, is that such a formula would need to be amended the moment you added or deleted any rows (and most users wouldn't remember to amend  it). And unfortunately, if you used { =SUM(D:D) } to total column D, you'd get a circular reference (the formula would include itself in the total). However, see: I tried to use the
{ =SUM(ABOVE) } formula in a table but the answer was obviously incorrect
for more information regarding this workaround.

Another workaround is to use ToolsCalculate instead of using a formula.

The trick I use is to have the total row as a separate table, separated from the main one by a paragraph with its Font size set to 1 point, and Hidden (under Format + Font), and its line spacing set to Multiple 0.1 (under Format + Paragraph).

Because of the 0.1 line spacing, the gap between the tables is not noticeable, even on-screen, and even when ShowAll is switched on; so arguably it isn't really vital that you set the font to Hidden; but without doing that, the gap may just be noticeable on high resolution printers.

Then use a bookmark in the SUM formula. It's a bit kludgy but it does work. One major benefit of this strategy (for things like Invoice and Quote templates) is that the user can subsequently add more rows to the main table simply by tabbing, and the formula will still work – which they couldn't otherwise do.

So supposing you've created two tables (the main table and the total row table) as follows:


Figure 1: This is actually two tables, separated by a 1 point, 0.1 line-spaced paragraph. 
As you can see, even with ShowAll switched on, the gap is invisible. 

Select the main table, select Insert + Bookmark, call the bookmark Table1 and click Add. (If you had more tables of this sort you could bookmark the others with names such as Table2, Table3, etc).

Then in the total cell, press Ctrl+F9, and within the field braces {}, insert the following formula:

{ SUM(Table1 F:F) \# "#,##0.00;- #,##0.00;''" }

Table1 in the formula refers to the name of the bookmark you've marked the main table with. The formula, being in a different table from the column it's totalling, wouldn't know which table you were referencing otherwise. The F:F bit means the 6th column from the left.

The bit at the end of the formula:

;''"

... means that if there are no values in column F, don't display anything (otherwise it would display 0.00). I learnt that trick from Word MVP Cindy Meister.

The total field will automatically update when you print, or go into Print Preview (provided you have  Update fields switched on under Tools + Options + Print); and you can also update your fields at any time by Selecting All and pressing F9 (update fields) - which you could assign to a Toolbar button to make it more user-friendly.

If the user might need to insert more such tables in the same document, you could make it more user-friendly still by storing the basic table layout, as an AutoText entry; and then using a macro to insert the AutoText entry, assign a bookmark to the main table (bearing in mind that the bookmark name needs to be different each time, so can't be stored in the AutoText entry itself), and insert the formula (which could not be stored in the AutoText entry, for the same reason).

Formula Fields are covered in more detail in Word's Help.


Click to view Terms of Use page

Click to view Disclaimer page