XL: Formula to Sum Digits of a Number

• Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel for OS/2, version 3.0
• Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
• Microsoft Excel 98 Macintosh 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.

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)))

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

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.

Note that 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.