## [Solved] Add numbers in the same cell

Discuss the spreadsheet application

### [Solved] Add numbers in the same cell

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
Last edited by Hagar Delest on Sat Sep 05, 2015 11:52 am, edited 1 time in total.
Reason: tagged [Solved].
open office 4.1.1 windows 10
Kacem

Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

### Re: add numbers in the same cell

You'd have to guarantee that the numbers used in the addition are always single digits.
Evren then it's not simple
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5403
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: add numbers in the same cell

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?
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP

RoryOF
Moderator

Posts: 29453
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

### Re: add numbers in the same cell

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.
open office 4.1.1 windows 10
Kacem

Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

### Re: add numbers in the same cell

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

My first idea:
Code: Select all   Expand viewCollapse view
`=LEFT(TEXT(A1;"000");1)+RIGHT(TEXT(A1;"000");1)+RIGHT(LEFT(TEXT(A1;"000");2);1)`
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 8225
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: add numbers in the same cell

Hallo
Code: Select all   Expand viewCollapse view
`=SUM(VALUE(MID(A1;ROW(\$A\$1:\$A\$3);1)))`

as Matrixformula
AOO4, Libreoffice - 5.1 … 5.3.2.2 on Linux Mint17

karolus
Volunteer

Posts: 852
Joined: Sat Jul 02, 2011 9:47 am

### Re: add numbers in the same cell

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   Expand viewCollapse view
`=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.)
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: add numbers in the same cell

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
open office 4.1.1 windows 10
Kacem

Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

### Re: add numbers in the same cell

Sorry, maybe I misunderstood your problem.
Do you have three cell with one digit in each cell
Code: Select all   Expand viewCollapse view
`| 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   Expand viewCollapse view
` | 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.))
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 8225
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: add numbers in the same cell

Kacem wrote:If there is formula to add numbers in the same cell. Example cell B1 258 formula that could bring me the total 15
Kacem wrote: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.

Kacem wrote: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.
Last edited by MrProgrammer on Tue Sep 15, 2015 6:04 am, edited 1 time in total.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3813
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: add numbers in the same cell

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.
open office 4.1.1 windows 10
Kacem

Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

### Re: add numbers in the same cell

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
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 8225
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: add numbers in the same cell

Hi Zizi64 thanks a lot for the help here a screen capture with the error message.
Attachments
open office 4.1.1 windows 10
Kacem

Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

### Re: add numbers in the same cell

The string of my example formula has contained English function names. but your locale setting is not english. Use the localized function names.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 8225
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: add numbers in the same cell

I got it.Sorted out. Thanks a lot for the help much nedeed. God Bless you
open office 4.1.1 windows 10
Kacem

Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

### Re: Add numbers in the same cell

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   Expand viewCollapse view
`=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   Expand viewCollapse view
`=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?

Attachments
sum error in b4.ods
OOo 4.1.3 on Windows 10 Home
CaliRay

Posts: 70
Joined: Sun Apr 26, 2009 6:20 pm

### Re: [Solved] Add numbers in the same cell

CaliRay for B4 wrote:=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
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3813
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: [Solved] Add numbers in the same cell

Thanks for the clarification and the working formula.
OOo 4.1.3 on Windows 10 Home
CaliRay

Posts: 70
Joined: Sun Apr 26, 2009 6:20 pm

### Re: [Solved] Add numbers in the same cell

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.
OpenOffice 4.1.6 Windows 7 64 bit.
Key WiteWolf

Posts: 3
Joined: Sat Jun 15, 2019 1:15 am

### Re: [Solved] Add numbers in the same cell

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.

The code of the macro function:

Code: Select all   Expand viewCollapse view
`REM  *****  BASIC  *****Option ExplicitFunction 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 = iSumend function`
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 8225
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: [Solved] Add numbers in the same cell

This seems to work based on Lupp's response above; adapt it as you need.
Attachments
numerology.ods
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 2941
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: [Solved] Add numbers in the same cell

robleyd wrote: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.

Zizi64 wrote: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.
OpenOffice 4.1.6 Windows 7 64 bit.
Key WiteWolf

Posts: 3
Joined: Sat Jun 15, 2019 1:15 am

### Re: [Solved] Add numbers in the same cell

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.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 2941
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: [Solved] Add numbers in the same cell

robleyd wrote:
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.
OpenOffice 4.1.6 Windows 7 64 bit.
Key WiteWolf

Posts: 3
Joined: Sat Jun 15, 2019 1:15 am