Sum of numbers of same cell and the result will be 0 to 9 or 11, 22, 33 or 44

[Redfish]

Hi,
I would like to know if you could help me create one formula to excel.
I would like to make a sum of each number of same cell.
The sum has to have the result of 0 to 9 or 11 or 22 or 33 or 44.
For example: If the cell has the number 911. The sum will be 9+1+1=11
For example if the number is 9996=9+9+9+6=33
For example if the numer is 23564=2+3+5+6+4=20=2+=2
In this moment I use this formula:
=If(REST(A1;9)=0;“9”;REST(A1;9)) (but the result will be from 0 to 9)
I need to add the exception if the result is 11 or 22 or 33 or 44
Can anyone help me?
I have the formula I can cover also the result of 11 but I need also to 22 or 33 or 44
=IF(A1=11;11;IF(REST(A1;9)=0;“9”;REST(A1;9)))
I have the same question in this link
https://www.excelguru.ca/forums/showthread.php?8749-Sum-of-numbers-of-same-cell-and-the-result-will-be-0-to-9-or-11-22-33-or-44
and in
https://www.excelforum.com/excel-fo...esult-will-be-0-to-9-or-11-22-33-or-44-a.html
Thanks.
Best regards

 

[wideboydixon]

Then again, maybe three iterations is safer:

Book1
	A	B	C	D
1	911	11	11	11
2	9996	33	33	33
3	23564	2	20	2
4	279998	44	44	44
5	22222	1	10	1
6	9999	9	36	9
7	12563	8	17	8
8	7777	1	28	10
Sheet1

Cell Formulas
Range	Formula
B1	=IF(OR(D1<10,D1=11,D1=22,D1=33,D1=44),D1,SUMPRODUCT(1*MID(D1,ROW(INDIRECT("1:"&LEN(D1))),1)))
C1	=SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
D1	=IF(OR(C1<10,C1=11,C1=22,C1=33,C1=44),C1,SUMPRODUCT(1*MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)))

WBD

 

[FormR]

At column D use this formula

Hi, I don't think this works for a number of 9999993 for example.

 

[heehaa]

Hi, I don't think this works for a number of 9999993 for example.

oh yea. forgotten about that. thanks FormR for the highlight. perhaps 2 helper will be the safer choice.

 

[wideboydixon]

A little digging suggests that 3 is ample. Also, I'm wondering whether 11, 22, 33 and 44 are special cases and should just return those values (rather than 2, 4, 6 and 8) so here's my final proposal which would show OK if you hide columns B and C:

Book1
	A	B	C	D
1	911	11	11	11
2	9996	33	33	33
3	23564	20	2	2
4	279998	44	44	44
5	22222	10	1	1
6	9999	36	9	9
7	199	19	10	1
8	9999993	57	12	3
9	994	22	22	22
Sheet1

Cell Formulas
Range	Formula
B1	=IF(OR(A1<10,A1=11,A1=22,A1=33,A1=44),A1,SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
C1	=IF(OR(B1<10,B1=11,B1=22,B1=33,B1=44),B1,SUMPRODUCT(1*MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)))
D1	=IF(OR(C1<10,C1=11,C1=22,C1=33,C1=44),C1,SUMPRODUCT(1*MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)))

WBD

 

[Redfish]

Thanks! It works!
Thank you so much to all!!!!

 

[FormR]

A little digging suggests that 3 is ample.

I thought that too when devising the suggestion in post 6.

 

[wideboydixon]

I thought that too when devising the suggestion in post 6.

https://en.wikipedia.org/wiki/Persistence_of_a_number
https://en.wikipedia.org/wiki/Digital_root

:D

 

[Macropod]

Perhaps with a single formula:

=IF(AND(MOD(SUM(--(0&MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},1))),11)=0,(SUM(--(0&MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},1)))<55)),SUM(--(0&MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},1))),SUM(--(0&MID(SUM(--(0&MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},1))),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},1))))