[Solved] Formula for sum for all digits?

[miksky]

I have found this formula working for sum of all digits forr three digit numbers:

=LEFT(TEXT(A2;"000");1)+RIGHT(TEXT(A2;"000");1)+RIGHT(LEFT(TEXT(A2;"000");2);1)

BUT it does not work for 4 digits.... 1968 becomes 18 and 968 becomes 23 ?!?!?!

Any fixes for this?

[Ed2]

If you want to continue with the formula you have then you just need to add another term for the third digit. Note that the "MID" function would be simpler than "RIGHT(LEFT...".

You could also use the "INT" and "MOD" functions. Try =INT(MOD(A2/1000;10))+INT(MOD(A2/100;10))+INT(MOD(A2/10;10))+INT(MOD(A2;10)).

You haven't said what you are trying to achieve, but there may be better ways of doing it than with a spreadsheet.

[Zizi64]

BUT it does not work for 4 digits....

---and what is the maximum number of the digits what you want to handle?

Maybe it is better to write your own macro for this task.
tips:
You can pass the integer number or the formatted string to your macro. The result will be an integer (or a long) type variable.

[miksky]

Thanks Ed2! Briliant! It works with the =INT(MOD(A4/1000;10))+INT(MOD(A4/100;10))+INT(MOD(A4/10;10))+INT(MOD(A4;10))
Thanks again!

[miksky]

zizi64 I needed 4 digits, but a formular for any number of digits would be cool, but for this purpose (I wanted to proove numerologi wrong on a specific claim they make)

[Lupp]

There is the "continued digit sum" where the digits of the sum of first order are added again and so on till only one digit remains.
For unsigned intergers written in decimal notation up to 14 digits you get it by

Code: Select all

=MOD(TheNumber;9)+IF(CURRENT()=0;9;0)

(This if you don't accept a result of zero.) "Thanks" automatic conversion the formula also works if the number is given as text.

For any usigned integer number given as a string of decimal digits of up to 65535 places given in cell A1 you get the not continued form by

Code: Select all

=SUMPRODUCT(VALUE(MID(A1;ROW(OFFSET(INDIRECT("a1");0;0;LEN(A1);1));1)))

and since 65535*9 is much less than 10^15 the result is exact and you get the continued digit sum by applying the first given formula only once subsequently:

Code: Select all

=MOD(SUMPRODUCT(VALUE(MID(A1;ROW(OFFSET(INDIRECT("a1");0;0;LEN(A1);1));1)));9)+IF(CURRENT()=0;9;0)

Appendix
-1- As far as I know in "numerology" often are used continued digit sums.
(Very personal now:)
-2- The fundamental approach of n. as I know it (mainly from other requests here) is so absurd for many reasons that I suspect no numerologist would need a next refutation to strengthen his belief. (Paradoxy intended.)
-3- No (next to) believer (in whatever) is interested in arguments - except they comply with his (f/m) position.
-4- I surely am also a believer who just didn't make some relevant beliefs explicit.
-5- Anyway I don't know what my brain will declare next to be my opinion.