Automatically totaling a column in a table

[Samantha]

I have a template in which I need the last column totaled. I cannot use the
atuo sum feature because not all of the rows will always be filled. I know I
can use the Tools Calculate feature, but since this form is going out to many
people, I was hoping that there would be a way to do it automatically, so
there is no chance of people forgetting how to do it. I cannot figure out
how to create a macro to do it for me.

 

[Barry]

Hello, Samantha:

If by "auto sum feature", you mean the Sum function, you can use it to total
a partially filled in column of figures. I just tried it and it works fine.
Just select a numeric format from the "Number" tab of the "Format Cells"
dialog box and use the formula

=SUM(Xn:Xm)

where

"X" is the column number and
"n" and m are the start and end rows of your set of numbers.

The blank cells are simply treated as zeros. (SUM can handle rows,
discontinuous ranges and multiple ranges in a single formula.

HTH

 

[Samantha]

When I try that it just says syntax error. I have calculations in the row
that I'm totaling, I'm not sure if that is effecting it at all.

 

[Barry]

Forgive me, Samantha. The method I described in my previous post is for
Escel. I forgot we were talking about Word here.

Is it not possible or convenient to enter a zero into the otherwise blank
cells? If you did that, the Auto Sum feature would work, of course.

You could write a macro that would check each cell in the range. If it
found a number it would add it to the sum. If the cell were ampty (or held
anything other than a number), it would be skipped. This is not something
you can do by recording keystrokes, however; you would need to do some VB
programming. Besides, the result would be a macro, not unlike
Tools/Calculate of Auto Sum, which the user would have to activate after
entering the numbers.

If you really want to a column of numbers that automatically recalculats its
total when the user adds or changes its contents, then you should use Excel.
Is that a problem for you

 

[Samantha]

I don't know very mcuh about macros, but I think I remember reading something
about a macro button. I can't remember what it was, but if I was able to
create the macro and just have a button that they could push, I think that
would be easier for people to remember. The document is a PO that we're
sending to vendors, and it looks a little sloppy to have zeroes in the empty
cells, and we're trying to keep it in Word to stay similar to our other
templates. If i'm not able to do this, I'll just have everyone add the total
manually, I just thought it would be easier if I could do it for them.

 

[Barry]

Samantha:

I see. Well, as I said, I believe these things can be done. Unfortunately,
they are not trivial tasks. Wou would need a VBA programmer to write the
macro.

Fortunately, the Word Programming discussion group is full of VBA
programmers. Ask your question there and you may get more useful answers.

 

[Samantha]

OK Thanks!

Samantha:

I see. Well, as I said, I believe these things can be done. Unfortunately,
they are not trivial tasks. Wou would need a VBA programmer to write the
macro.

Fortunately, the Word Programming discussion group is full of VBA
programmers. Ask your question there and you may get more useful answers.

 

[Tom Conrad]

Samantha,

The following reply is copied from a reply to a similar question

Question: How to input formulas into a sales invoice tamplet from Word?
Posted by: Rosy3392
Posted 7/11/2007

Tom C replied:

Take a look at the following article:
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.

Article By Dave Rado
<http://word.mvps.org/FAQs/TblsFldsFms/TotalColumn.htm >

This article suggests two methods to achieve the effect you desire within
Word without embedding an Excel spreadsheet. Both methods use a mixture of
bookmarks and field codes.

The numbers to be summed are placed in a data table (Table01), and the
formulae are placed into a summation table.

The tables are separated by a blank paragraph. The article specifies a line
height for the paragraph. I would also recommend the font size should be set
to 1pt.

The total numbers of rows in table 01 can be varied with out disrupting the
formulae.

Tom C