Article ID: 108107

Article Last Modified on 8/15/2003

APPLIES TO

- Microsoft Excel 97 Standard Edition

This article was previously published under Q108107

## 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.

## MORE INFORMATION

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