# Microsoft KB Archive/108107

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
 Knowledge Base

# Formula to Sum Digits of a Number

Article ID: 108107

APPLIES TO

• Microsoft Excel 97 Standard Edition

## SUMMARY

Although there is currently no built-in formula that finds the sum of the digits of a number in Microsoft Excel, you can use the formula in the "More Information" section in this article to do this.

The following formula returns the sum of the digits of a positive number contained in cell A10:

```   =SUM(VALUE(MID(A10,ROW(\$A\$1:OFFSET(\$A\$1,LEN(A10)-1,0)),1)))
```

The following formula returns the sum of the digits of a negative number contained in cell A11:

```   =SUM(VALUE(MID(A11,ROW(\$A\$2:OFFSET(\$A\$2,LEN(A11)-2,0)),1)))
```

NOTE: The above formulas must be entered as array formulas. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.

### Explanation of the Formula

The following information assumes that cell A1 contains the number 849.

```   This part of the formula  Does this

------------------------------------------------------------------------

A\$1:OFFSET(\$A\$1,LEN(A1)-  Creates a reference of cells going down a
1,0)                      column that has the same number of cells as
the number in A1 has digits. For example, the
formula \$A\$1:OFFSET(\$A\$1,LEN(A1)-1,0) would
return \$A\$1:\$A\$3 since LEN(A1)-1 equals 2 and
OFFSET(\$A\$1,2,0) returns \$A\$3.

ROW()                     Returns the row number of the cell reference.
If there is more than one cell in the
reference, it will return an array. In this
case, ROW(\$A\$1:\$A\$3) returns {1;2;3}.

MID()                     Returns a portion of a text string. By using
an array for one of the arguments, we can
return multiple text strings in an array. For
example, consider MID(849,{1;2;3},1) from the
above paragraph. This will return
{"8";"4":"9"}. Notice that all the numbers are
text inside the array.

VALUE()                   Will change text to numbers. In this example,
VALUE({"8";"4":"9"}) returns {8;4;9}. This
allows the numbers to be summed.

SUM({8;4;9})              Returns the final result, 21.
```

The second formula, which sums the digits of negative numbers, works in the same manner except that it compensates for the initial minus sign (-) in the number.

NOTE: In each formula, the "\$A\$1" and "\$A\$2" are always used, regardless of which cell's digits are being summed.

## REFERENCES

"Function Reference," version 4.0, pages 274, 299-300, 365-366, 423, 444.

Additional query words: 2.0 2.10 4.00a 5.00a 5.00c 7.00a 97 XL97 XL7 XL5 XL4 XL3 XL

Keywords: kbhowto KB108107
The text passage provides a detailed explanation of how to use formulas in Microsoft Excel to sum the digits of a number, including both positive and negative numbers. It includes the formulas and a step-by-step breakdown of how they work.
no_question

the passage does not contain a mathematical question but rather provides instructions and explanations for using specific excel formulas.
Given a text passage, determine:
    1. If it contains a mathematical question (including word problems or calculations)
    2. If an answer to that mathematical question is provided in the text

First, describe the content structure you found in 1-2 sentences.

Then provide your analysis in the following format:
TYPE: <one of the following values>
- NO_QUESTION
- SINGLE_QUESTION
- SINGLE_QUESTION_WITH_ANSWER
- MULTIPLE_QUESTIONS
- MULTIPLE_QUESTIONS_WITH_ANSWERS
system
# Microsoft KB Archive/108107

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
 Knowledge Base

# Formula to Sum Digits of a Number

Article ID: 108107

APPLIES TO

• Microsoft Excel 97 Standard Edition

## SUMMARY

Although there is currently no built-in formula that finds the sum of the digits of a number in Microsoft Excel, you can use the formula in the "More Information" section in this article to do this.

The following formula returns the sum of the digits of a positive number contained in cell A10:

```   =SUM(VALUE(MID(A10,ROW(\$A\$1:OFFSET(\$A\$1,LEN(A10)-1,0)),1)))
```

The following formula returns the sum of the digits of a negative number contained in cell A11:

```   =SUM(VALUE(MID(A11,ROW(\$A\$2:OFFSET(\$A\$2,LEN(A11)-2,0)),1)))
```

NOTE: The above formulas must be entered as array formulas. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.

### Explanation of the Formula

The following information assumes that cell A1 contains the number 849.

```   This part of the formula  Does this

------------------------------------------------------------------------

A\$1:OFFSET(\$A\$1,LEN(A1)-  Creates a reference of cells going down a
1,0)                      column that has the same number of cells as
the number in A1 has digits. For example, the
formula \$A\$1:OFFSET(\$A\$1,LEN(A1)-1,0) would
return \$A\$1:\$A\$3 since LEN(A1)-1 equals 2 and
OFFSET(\$A\$1,2,0) returns \$A\$3.

ROW()                     Returns the row number of the cell reference.
If there is more than one cell in the
reference, it will return an array. In this
case, ROW(\$A\$1:\$A\$3) returns {1;2;3}.

MID()                     Returns a portion of a text string. By using
an array for one of the arguments, we can
return multiple text strings in an array. For
example, consider MID(849,{1;2;3},1) from the
above paragraph. This will return
{"8";"4":"9"}. Notice that all the numbers are
text inside the array.

VALUE()                   Will change text to numbers. In this example,
VALUE({"8";"4":"9"}) returns {8;4;9}. This
allows the numbers to be summed.

SUM({8;4;9})              Returns the final result, 21.
```

The second formula, which sums the digits of negative numbers, works in the same manner except that it compensates for the initial minus sign (-) in the number.

NOTE: In each formula, the "\$A\$1" and "\$A\$2" are always used, regardless of which cell's digits are being summed.

## REFERENCES

"Function Reference," version 4.0, pages 274, 299-300, 365-366, 423, 444.

Additional query words: 2.0 2.10 4.00a 5.00a 5.00c 7.00a 97 XL97 XL7 XL5 XL4 XL3 XL

Keywords: kbhowto KB108107
user
The text passage provides a detailed explanation of how to use formulas in Microsoft Excel to sum the digits of a number, including both positive and negative numbers. It includes the formulas and a step-by-step breakdown of how they work.

TYPE: NO_QUESTION

The passage does not contain a mathematical question but rather provides instructions and explanations for using specific Excel formulas.