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

 

[FormR]

Hi, welcome to the forum!

The sum has to have the result of 0 to 9 or 11 or 22 or 33 or 44.

So the sum has to 0-9 or a multiple of 11? What happens if the result of the sum is not one of those cases?

For example if the numer is 23564=2+3+5+6+4=20=2+=2

I don't follow this? Can you elaborate?

 

[Redfish]

Thank you.
The cell for exemple have the number 12563 so the other cell will sum 1+2+5+6+3 until get only one digit but if the result of two digits is 11, or 22 or 33 or 44 the result will be the number 11, or 22 or 33 or 44.
In this case 1+2+5+6+3=17 => 1+7=8
In this example if I have this formula it works well
=If(REST(A1;9)=0;“9”;REST(A1;9)) (but the result will be from 0 to 9)
But I do not know how to make the exception of 11, 22, 33, 44.

 

[wideboydixon]

Not sure you can do it with a formula due to the recursive nature. I wrote a small UDF that would do it I think:

Public Function SumNumbers(cellValue As Variant)

Dim i As Long

SumNumbers = 0

For i = 1 To Len(cellValue)
    SumNumbers = SumNumbers + Mid(cellValue, i, 1)
Next i

Select Case SumNumbers
    Case 0 To 9, 11, 22, 33, 44
    Case Else
        SumNumbers = SumNumbers(SumNumbers)
End Select

End Function

Book1
	A	B
1	911	11
2	9996	33
3	23564	2
4	279998	44
Sheet1

Cell Formulas
Range	Formula
B1	=SumNumbers(A1)

WBD

 

[Redfish]

Hi,
Thak you for the help.
My excel file is protected so I canot add one UDF.
I need a formula.
Best regards

 

[FormR]

My excel file is protected so I canot add one UDF.
I need a formula.

Hi, in that case I think you'll need to use some helper columns.

Excel 2013/2016
	A	B	C	D	E
1	Number	Result	Helper 1	Helper 2	Helper 3
2	911	11	11
3	9996	33	33
4	23564	2	20	2
5	23564	2	20	2
6	23564	2	20	2
7	279998	44	44
8	22222	1	10	1
9	9999	9	36	9
10	12563	8	17	8
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(9.99E+307,C2:E2)
C2	=SUM(0+("0"&MID($A2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1)))
D2	=IF(OR(C2="",C2<10,C2={11,22,33,44}),"",SUM(0+("0"&MID(C2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1))))
E2	=IF(OR(D2="",D2<10,D2={11,22,33,44}),"",SUM(0+("0"&MID(D2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1))))

 

[Special-K99]

If you just want to add up the digits of a number in a cell then this works

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

 

[heehaa]

with 1 helper at column C using formula

=SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

At column D use this formula

=IF(AND(LEN(SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))=2,SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>11,SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>9,SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>22,SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>33,SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>44),SUMPRODUCT(1*MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)),SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

 

[Redfish]

with 1 helper at column C using formula

=SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

At column D use this formula

=IF(AND(LEN(SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))=2,SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>11,SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>9,SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>22,SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>33,SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>44),SUMPRODUCT(1*MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)),SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

Hi,
I tryed the second formula and I canot value the formula.
Could you confirm if it is ok.
Thanks.
Best regards

 

[wideboydixon]

If you're happy to use a helper column and we assume that we only need two iterations at most then you could do this:

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

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

WBD