How to add digits in a number, till it becomes single digit number

**ambaji7** · 09-15-2012, 12:26 AM

I need to calculate the sum of digits in a number. The function should keep on adding the digits of each result, till the final sum is a single digit. Please refer to the following example and result expected:-

Supposed the number entered in Cell J1 is 1234589755

Result 1 = 1+2+3+4+5+8+9+7+5+5 = 49

Result 2 = 4+9 = 13

Final Result required = 1+3 = 4

Regards
Ambaji Rao S.

**icestationzbra** · 09-15-2012, 12:43 AM

see if this helps:

http://www.excelforum.com/excel-gene...ne-number.html

**vlady** · 09-15-2012, 12:46 AM

Hello
maybe like this.

summcontentofcell.xlsx

Hth

**squiggler47** · 09-16-2012, 09:41 AM

Or if you want to go over board with one formula :

=SUMPRODUCT(--MID(SUMPRODUCT(--MID(SUMPRODUCT(--MID(SUMPRODUCT(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),ROW(INDIRECT("1:"&LEN(SUMPRODUCT(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))))),1)),ROW(INDIRECT("1:"&LEN(SUMPRODUCT(--MID(SUMPRODUCT(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),ROW(INDIRECT("1:"&LEN(SUMPRODUCT(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))))),1))))),1)),ROW(INDIRECT("1:"&LEN(SUMPRODUCT(--MID(SUMPRODUCT(--MID(SUMPRODUCT(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),ROW(INDIRECT("1:"&LEN(SUMPRODUCT(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))))),1)),ROW(INDIRECT("1:"&LEN(SUMPRODUCT(--MID(SUMPRODUCT(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),ROW(INDIRECT("1:"&LEN(SUMPRODUCT(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))))),1))))),1))))),1))

Since 15x9 = 145 and excel can only display 15 digits we just need to sum the digits 3 times to ensure we have a single digit answer!

**shg** · 09-16-2012, 10:26 AM

I'm not sure that's an improvement over =A1 - FLOOR(A1 - 1, 9), Squiggler.

=MOD(A1-1, 9) + 1 would work equally well but for a bug in the MOD function (http://support.microsoft.com/kb/119083)