Jan 25 2017 06:27 AM
All cells in the Column contain more than one value in each cell , that they were entered by using Alt+enter ..
I want a formula to sum the values in each Cell ..
Exmp.
the Cell A1 contains 100 200 300 , Cell A2 contains 500 300 ,Cell A3 contains 500 , Cell A4 contains 100 150 200 50 ..etc.
I need to add Column showing the total in each Cell , i.e. B1 shows 600 , B2 800 , B3 500 , B4 500 ..etc.
what is the formula I have to use in Column B .I reached to get it 100+200+300 ..etc i.e. replace the space or Alt +Enter by "+" , but I can not calculate it to get the total .
Jan 25 2017 07:11 AM
Abdullah, perhaps this http://stackoverflow.com/questions/25316094/split-a-string-cell-in-excel-without-vba-e-g-for-array-f... idea with array will help, but i dind't try that myself
Jan 25 2017 11:02 AM - edited Jan 25 2017 11:03 AM
Played a bit taken the approach from here https://www.mrexcel.com/forum/excel-questions/505622-text-numbers-same-cell-sum-numbers.html
For simplicity let assume your numbers are split by spaces, not by ALT+ENTER.
In A2 you have "200 3 4 6 2"
When
=SUMPRODUCT(
IF(
SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",10)),{1,2,3,4,5,6,7,8}*10-9,10)),",","")="",
0,
SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",10)),{1,2,3,4,5,6,7,8}*10-9,10)),",","")*1)
)
gives 215.
Here 10 in REPT is any number which is more than length of your biggets number
Array {1,2,..} is more than biggest number of numbers in your cells
First SUBSTITUTES compares with IF set extra members of array to 0 (othervise you'll have an error)
*1 close to end converts string array with numbers into the numeric array
You may use Evaluate Formula in Ribbon to check how it works.
And i guess you may use directly ALT+ENTER symbol in final formula
Jan 25 2017 11:06 AM
Forgot to say that's not an array formula, i.e. without ctrl+shift+enter
Jan 29 2017 10:32 PM
thank you very much , could you please send me it in Excel ..
Jan 30 2017 12:19 AM
Attached
Jan 30 2017 03:41 AM
It is not working fine , I tried different values like 100 1600 2000 in the cell it gave me value 1900 ..
Jan 30 2017 04:00 AM
Please expand your range in formula, e.g. use 100 instead of 10, like
...,REPT(" ",100)),{1,2,3,4,5,6,7,8}*100-99,100))
instead of
...,REPT(" ",10)),{1,2,3,4,5,6,7,8}*10-9,10))
When you'll have 3700 for "100 1600 2000". This magic 100 (or whatever) shall be more than maximum number of characters in any of your cells.
Jan 30 2017 04:39 AM
Hi Abdullah,
If you have access to Power Query then that can also provide a possible solution
Power Query can be used to split cells containing Alt+Enter and then a few extra Power Query steps allows you to sum those figures. I've attached a demo file
Jan 30 2017 04:46 AM
I agree with Wyn, with Power Query it's easier - if you use it