Excel sum numbers from just one cell problem

%3CLINGO-SUB%20id%3D%22lingo-sub-1620873%22%20slang%3D%22en-US%22%3EExcel%20sum%20numbers%20from%20just%20one%20cell%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620873%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EIn%20one%20cell%2C%20I%20have%20something%20like%20this%3A%201*5%2B30%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20want%20some%20other%20cell%20to%20display%20sum%20of%20the%20content%20in%20that%20cell.%20%3DSUM(A2)%20does%20not%20work.%20How%20to%20do%20that%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1620873%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1620966%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20sum%20numbers%20from%20just%20one%20cell%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620966%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F775606%22%20target%3D%22_blank%22%3E%40ensarhamzic%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%20%3CEM%3EIn%20one%20cell%2C%20I%20have%20something%20like%20this%3A%201*5%2B30%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EI%20want%20some%20other%20cell%20to%20display%20sum%20of%20the%20content%20in%20that%20cell.%20%3DSUM(A2)%20does%20not%20work.%20How%20to%20do%20that%3F%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20may%20I%20ask%20you%20what%20the%20answer%20should%20be%3F%20What%20are%20you%20expecting%20as%20the%20%22sum%20of%20the%20content%20in%20that%20cell%22%3F%3C%2FP%3E%3CUL%3E%3CLI%3E36%3F%20(1%2B5%2B30%2C%20a%20sum%20of%20the%20numbers%2C%20but%20ignoring%20the%20non-numerics)%3C%2FLI%3E%3CLI%3E35%3F%20(multiplying%201%20times%205%20and%20then%20adding%2030)%3C%2FLI%3E%3CLI%3E35%3F%20(adding%205%2B30%20and%20then%20multiplying%20by%201)....in%20this%20instance%2C%20because%20of%20the%201%20as%20the%20multiplier%2C%20the%20last%20two%20are%20the%20same%2C%20but%20that%20would%20not%20be%20true%20if%20you%20had%20begun%20with%2C%20say%2C%20a%202%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20any%20event%2C%20why%20don't%20you%20have%20cell%20A2%2C%20which%20presumably%20is%20where%20you%20figures%20appear%2C%20written%20as%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D1*5%2B30%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIf%20you%20did%2C%20you'd%20be%20getting%20the%20result%20right%20there%20(35).%20As%20it%20is%2C%20%3CU%3Eby%20entering%20it%20without%20the%20preceding%20equals%20sign%2C%20you're%20de%20facto%20treating%20it%20as%20text.%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20let's%20see%20if%20we%20can%20get%20a%20little%20background%20here.%20%3CSTRONG%3EWHY%3C%2FSTRONG%3E%20are%20you%20entering%20that%20first%20string%20of%20characters%2C%20some%20of%20which%20happen%20to%20be%20numbers%2C%20in%20that%20fashion%3F%20There%20are%20ways%20to%20handle%20this%2C%20but%20before%20we%20get%20to%20that%2C%20there%20may%20also%20be%20a%20much%20better%20way%20to%20get%20to%20the%20desired%20result.%3C%2FP%3E%3CUL%3E%3CLI%3ESo%20(for%20example)%20are%20you%20a%20teacher%20wanting%20to%20write%20down%20a%20problem%20for%20students%20to%20solve%2C%20and%20therefore%20wanting%20to%20enter%20it%20in%20cell%20A2%20as%20you%20have%20done%20it%3F%3C%2FLI%3E%3CLI%3EWill%20you%20be%20entering%20other%20%22problems%22%20in%20that%20fashion%2C%20and%20then%20wanting%20to%20test%20results%20by%20a%20formula%20elsewhere%2C%20maybe%20right%20adjacent%2C%20on%20the%20sheet%3F%3C%2FLI%3E%3CLI%3EIn%20short%2C%20what's%20this%20a%20part%20of%3F%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1621016%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20sum%20numbers%20from%20just%20one%20cell%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1621016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F775606%22%20target%3D%22_blank%22%3E%40ensarhamzic%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20what%20exactly%20do%20you%20mean%2C%20as%20variant%20you%20may%20use%20named%20formula%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20461px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215388i1A72C75905BB8C86%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethe%20only%20the%20file%20shall%20be%20saved%20as%20macro-enabled.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello.
In one cell, I have something like this: 1*5+30
I want some other cell to display sum of the content in that cell. =SUM(A2) does not work. How to do that?

2 Replies

@ensarhamzic 

 

You wrote: In one cell, I have something like this: 1*5+30
I want some other cell to display sum of the content in that cell. =SUM(A2) does not work. How to do that?

 

So may I ask you what the answer should be? What are you expecting as the "sum of the content in that cell"?

  • 36? (1+5+30, a sum of the numbers, but ignoring the non-numerics)
  • 35? (multiplying 1 times 5 and then adding 30)
  • 35? (adding 5+30 and then multiplying by 1)....in this instance, because of the 1 as the multiplier, the last two are the same, but that would not be true if you had begun with, say, a 2

 

In any event, why don't you have cell A2, which presumably is where you figures appear, written as

=1*5+30

If you did, you'd be getting the result right there (35). As it is, by entering it without the preceding equals sign, you're de facto treating it as text.

 

So let's see if we can get a little background here. WHY are you entering that first string of characters, some of which happen to be numbers, in that fashion? There are ways to handle this, but before we get to that, there may also be a much better way to get to the desired result.

  • So (for example) are you a teacher wanting to write down a problem for students to solve, and therefore wanting to enter it in cell A2 as you have done it?
  • Will you be entering other "problems" in that fashion, and then wanting to test results by a formula elsewhere, maybe right adjacent, on the sheet?
  • In short, what's this a part of?

 

@ensarhamzic 

Not sure what exactly do you mean, as variant you may use named formula as

image.png

the only the file shall be saved as macro-enabled.