Ignore Blank Cells When doing Sum(ABOVE) in Table

[Katherine Coombs]

Hi all,

I've got a column in my table with blank cells, but I want to sum the entire
column. For example:

qwer 123
tyui 456

iop 789
TOTAL $XXX

where XXX should be 123 + 456 + 789

I know that I can set it to =sum(B:B) but then if I update it, it doubles up
because it includes itself in that total. I can also do =sum(B1:B4) but
then the problem is that I'm also needing to insert rows just above the
formula and I don't want to have to update the formula each time.

So, unless there's another suggest I need to do a =SUM(ABOVE) but I need it
to skip over the blanks (or count them as zero) and keep adding every number
in the column. ie don't stop at 789 in the example above.

Any ideas? Sorry if this has been answered in earlier posts but I don't
have Net access to check in google or in the KB.

TIA,
Katherine

 

[Jay Freedman]

Hi, Katherine,

See http://www.mvps.org/word/FAQs/TblsFldsFms/TotalColumn.htm.

 

[Katherine Coombs]

Hi there,

Hate to be a pain but as per the last para in my post I have no Net access.
I know, I know....

Can you cut and paste it in your reply to me?

Thanks!
Katherine

 

[Doug Robbins - Word MVP]

Hi Katherine,

Here's a copy paste of the article to which you were referred by Jay. It
looses a lot from the original. I would suggest that you write down the URL
that he gave you and go to:

(a) a friends house
(b) your local internet cafe

and take a look at the original.

Quote

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(D120) }. 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) } 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.

Unquote

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Hi there,

Hate to be a pain but as per the last para in my post I have no Net access.
I know, I know....

Can you cut and paste it in your reply to me?

Thanks!
Katherine

Hi, Katherine,

See http://www.mvps.org/word/FAQs/TblsFldsFms/TotalColumn.htm.

doubles

up

need