[Solved] Add numbers in the same cell

[Kacem]

If there is formula to add numbers in the same cell. Example cell B1 258 formula that could bring me the total 15, because 2+5+8=13 thanks in advance for any help

[RusselB]

You'd have to guarantee that the numbers used in the addition are always single digits.
Evren then it's not simple

[RoryOF]

Also, what do you intend should happen? Taking your example, the extra number is 2. What happens to the 2? Is it to be inserted before/after the existing string of numbers (not possible) or is it to be placed in another cell? Will the limiting number (15) always be the same?

[Kacem]

Thanks for the replies. It will be 3 numbers single digit, any numbers from 1....9. As in the example. I want to automat them because there are a long columns. another example 113=5 418=13. Thanks in any way.

[Zizi64]

When that 3 digit integer number is located in cell A1, put the next formula into the B1:

My first idea:

Code: Select all

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

[karolus]

Hallo

Code: Select all

=SUM(VALUE(MID(A1;ROW($A$1:$A$3);1)))

as Matrixformula

[Lupp]

What you want to get is the "cross sum". This is a well defined function in ("lower") number theory used for statements concerning divisibility of numbers represented in a g-adic system, mostly the decimal system. As we well are able to decide the divisibilty for a three-digit-number directly by mental arithmetics, we might be interested to get the cross sum for numbers of arbitrary length.
Having a nonnegative integer of arbitrary length in A1 again this is achieved by

Code: Select all

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

independet of whether the number in A1 is actually a number or a text consisting of decimal digits and representing the number. If the number is, however, too large to be handled as an integer by the arithmetic engine of Calc/TheComputer, it must be text. For the TEXT 12345678987654321002468013579 e.g. we get the correct cross sum 126 while the "same" number entered without precautions to get it as text will produce an error 502.

You may call the construct I used here a virtual for-statement.

Editing
Just found the correct answer by karolus. Of course, he is also capable of breking the condition of "three digits exactly".
(Using SUMPRODUCT() allows for omitting the explicit marking the expression for matrix evaluation.)

[Kacem]

thanks zizi64 for your answer didn't work. I don't have text only 3 numbers column and want the sum of the digit ex: 112 equal to 4; 321 equal to 6

[Zizi64]

Sorry, maybe I misunderstood your problem.
Do you have three cell with one digit in each cell

Code: Select all

| 1 | 2 | 3 | 

or do you have one cell with a three digit number (or three character legth string of numeric characters)?

Code: Select all

 | 123 |

((The word 'TEXT' - in my example - is the name of a function for converting a number to formatted string. I am using English function names.))

[MrProgrammer]

If there is formula to add numbers in the same cell. Example cell B1 258 formula that could bring me the total 15

It will be 3 numbers single digit, any numbers from 1....9.

Assuming that your cell is A1, all of these work for me based on my understanding of the situation:

=A1-INT(A1/100)*9-INT(A1/10)*9
=A1-9*SUMPRODUCT(INT(A1/{10;100}))
=LEFT(TEXT(A1;"000");1)+RIGHT(TEXT(A1;"000");1)+RIGHT(LEFT(TEXT(A1;"000");2);1) ← from Zizi64
=SUM(VALUE(MID(A1;ROW($A$1:$A$3);1))) ← from karolus
=SUMPRODUCT(VALUE(MID(A1;ROW(OFFSET(A1;0;0;LEN(A1);1));1))) ← from Lupp

where the green formula must be entered as an Array formula.

thanks zizi64 for your answer didn't work.

"didn't work" is not helpful in the forum because it tells us what did not happen. Please never use that phrase in a post. We need to know exactly what actions you took, what did happen, and what you expected to happen. Attaching a document demonstrating the problem is almost always helpful for us and will get your problem solved more quickly. If you typed a formula, pressed Enter, and got #VALUE!, tell us that. Then attach your document so we can see what's wrong. Often "it didn't work" means "it didn't do what I expected" and the real problem is that the program is behaving correctly but your expectations are incorrect. To help you solve the problem we need to know your expectations and your ultimate goal.

How to attach a document
Remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself.

Read section 1. Types of data in Ten concepts that every Calc user should know. You need to tell us if your cell contains a number or text.

[Kacem]

Maybe I didn't make myself understood. I am a real beginners in spreadsheet, please don't blame if I don't express myself the right way. In cell
A1 I have 123 in B1 I want 6 (result)
A2 222 B2 6
A3 215 B3 8
..
..
My formula in B1 should give what I was expecting 6. I tried all the formulas sent to me but not the right answer. Thanks at all of you trying to help me.

[Zizi64]

Please upload your example .ods file with your typed formulas here
I can not imagine, what is wrong in your file, but my formula works for me, as you expected (I not tried the other suggested formulas).

digits.ods

(8.9 KiB) Downloaded 152 times

[Kacem]

Hi Zizi64 thanks a lot for the help here a screen capture with the error message.

[Zizi64]

The string of my example formula has contained English function names. but your locale setting is not english. Use the localized function names.

[Kacem]

I got it.Sorted out. Thanks a lot for the help much nedeed. God Bless you

[CaliRay]

I have the same basic request as the original but there seems to be a twist. I have two numbers, 111943 and 1101943. Both add up to 19. The the first number sums correctly at 19 but the second one results in 18.

Numbers are in cells A3 and A4. Formulas in cells B3 and B4.

$B3

Code: Select all

=INT(MOD($A3/100000;10))+INT(MOD($A3/10000;10))+INT(MOD($A3/1000;10))+INT(MOD($A3/100;10))+INT(MOD($A3/10;10))+INT(MOD($A3;10))

$B4

Code: Select all

=INT(MOD($A4/100000;10))+INT(MOD($A4/10000;10))+INT(MOD($A4/1000;10))+INT(MOD($A4/100;10))+INT(MOD($A4/10;10))+INT(MOD($A4;10))

Tried variations of the B3 formula in B4 due to the number in B4 being 7 digits instead of 6 but nothing gave the correct sum. Is the zero the problem? If so, can you explain what happens when a zero is found in the number?

Thank you for your advice.

[MrProgrammer]

=INT(MOD($A4/100000;10))+INT(MOD($A4/10000;10))+INT(MOD($A4/1000;10))
+INT(MOD($A4/100;10))+INT(MOD($A4/10;10))+INT(MOD($A4;10))

1101943 has seven digits so you need seven terms:
=INT(MOD($A4/1E6;10))+INT(MOD($A4/1E5;10))+INT(MOD($A4/1E4;10))
+INT(MOD($A4/1E3;10))+INT(MOD($A4/1E2;10))+INT(MOD($A4/1E1;10))+INT(MOD($A4;10))

I prefer: =A4-9*SUMPRODUCT(INT(A4/{1E1;1E2;1E3;1E4;1E5;1E6}))
Or put 1E1 through 1E6 in six cells, X1:X6, and use =A4-9*SUMPRODUCT(INT(A4/$X$1:$X$6))

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[CaliRay]

Thanks for the clarification and the working formula.

[Key WiteWolf]

Greetings and salutations.
Like the previous two users who asked, I also need help with a formula that will add numbers within the same cell. But the problem is, most of the formulas presented here seem to be based on the number of digits being a constant. Exactly 3 digits for the first user, and it looks like 6 and 7 digits for the second. The problem is, I need a formula that will add the numbers in a cell together individually when the number of digits varies. Because it will keep changing, and I need the calculation to keep up with that. Just to clarify, this is like the other two, in that I am wanting a single cell with something like 123 to return a result of 6 in the next cell. For anyone wanting to give examples, the first single cell in my current project for this is B2, with the result in C2. But I would love if someone explains what the parts of the formula do if an example is given. Since none of the other formulas here broke down the interaction of the parts making it up, and they were all so different from each other, I could not figure out how to modify it for my needs (though I did try for a while). Thank you in advance for your time.

[Zizi64]

The problem is, I need a formula that will add the numbers in a cell together individually when the number of digits varies.

Try to write your own macro function for this task. You can convert the passed integer number to string format, then you can enumerate the number of the characters. Then a loop (FOR-NEXT or DO-WHILE) can get each characters one-by-one from the text, and it can convert them back to numetic value, finally it will add them.

AddNumbers.ods

(10.02 KiB) Downloaded 144 times

The code of the macro function:

Code: Select all

REM  *****  BASIC  *****

Option Explicit

Function AddNumbers(iTheNumber as long) as Integer

 Dim iSum as long
 Dim iLenght as integer
 Dim i as integer
 Dim iOneNumber as integer
 Dim sTheNumber as string 
 Dim sOneChar as string

	sTheNumber = Str(Abs(iTheNumber)) 'Supress the "-" sign when the input is a negative number
	iLenght = Len(sTheNumber)
	iSum = 0
	For i = 2 to iLenght ' from 2:  Because the converted string has one plus character for the "+" sign, but the "+" is not appeared, it is a whitespace)
		sOneChar = Mid(sTheNumber,i,1)
		iOneNumber = Val(sOneChar)
		iSum = iSum + iOneNumber
	Next i	
	
AddNumbers = iSum
end function

[robleyd]

This seems to work based on Lupp's response above; adapt it as you need.

[Key WiteWolf]

This seems to work based on Lupp's response above; adapt it as you need.

While this won't help in this specific case (because the number of digits keeps changing), this is very helpful for future projects where I actually *would* know the number of digits ahead of time, or where they are a fixed length. So thank you. For more "normal" projects, this is a very useful and handy list/guide.

Try to write your own macro function for this task. You can convert the passed integer number to string format, then you can enumerate the number of the characters. Then a loop (FOR-NEXT or DO-WHILE) can get each characters one-by-one from the text, and it can convert them back to numetic value, finally it will add them.

Well that's cool. I wasn't aware Calc actually allowed this level of control/alteration to the functions. But then I haven't seen many people talk about using macros in it so far. At least not ones this detailed. Thank you very much. I think this is what I needed. I am pretty sure I can work with it now and alter if needed for future projects. I really appreciate the explanation.

[robleyd]

While this won't help in this specific case (because the number of digits keeps changing)

You may not have noticed, but the formula is the same for each row and is capable of handling varying length numbers. I simply entered a number of rows with different length numbers by way of example to demonstrate the capabilities of the formula.. Try, for example, changing A1 to a different number of digits and see what happens.

[Key WiteWolf]

While this won't help in this specific case (because the number of digits keeps changing)

You may not have noticed, but the formula is the same for each row and is capable of handling varying length numbers. I simply entered a number of rows with different length numbers by way of example to demonstrate the capabilities of the formula.. Try, for example, changing A1 to a different number of digits and see what happens.

Ah okay, I see where I misunderstood. My apologies. Yes, this works. Thank you. Though it is good to know I can also create macros, this method is probably faster for my current situation. Thanks for pointing it out again.