Microsoft KB Archive/192872

= Description of the formula to sum digits of a number in Excel for Mac =

Article ID: 192872

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Excel 2004 for Mac
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q192872





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, 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 that in each formula, the "$A$1" and "$A$2" are always used, regardless of which cell's digits are being summed.

Additional query words: XL98 XL2001 XLX XL2004

Keywords: kbhowto KB192872

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.