Knowledge Base

WD: SUM Formula Does Not Update Correctly

Article ID: 118521

Article Last Modified on 8/16/2005

APPLIES TO

This article was previously published under Q118521

SYMPTOMS

The SUM formula field may update incorrectly if text exists in a cell of a row or column when using one of the following predefined formula fields:

=SUM(LEFT)
=SUM(RIGHT)
=SUM(BELOW)
=SUM(ABOVE)

NOTE: Text is considered to be any non-number character, including a footnote reference mark.

In the following table, the first and second rows will update correctly. If the text is in a cell immediately adjacent to the formula, the calculation updates correctly. 
   ---------------------------------------------------------
   |      1      |      2      |      3      | =SUM(LEFT)  |
   ---------------------------------------------------------
   |      1      |      2      |    TEXT     | =SUM(LEFT)  |
   ---------------------------------------------------------
   |      1      |    TEXT     |      3      | =SUM(LEFT)  |
   ---------------------------------------------------------
The third row produces an incorrect result, as shown in the table below. 
   ---------------------------------------------------------
   |      1      |      2      |      3      |      6      |
   ---------------------------------------------------------
   |      1      |      2      |     TEXT    |      3      |
   ---------------------------------------------------------
   |      1      |    TEXT     |      3      |      3      |
   ---------------------------------------------------------

WORKAROUND

To work around this problem, use either of the following methods.

Method 1

Specify a range of cells in your formula, for example:

{=sum(a1:a3)}

Method 2

Specify a series of cells in your formula, for example:

{=sum(a1,a2,a3)}


Additional query words: formula expression incorrect calculate calculation

Keywords: kbualink97 kbprb kbfield kbformula KB118521


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.