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.
