Formula to Sum Digits of a Number

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.