1

In 2 cells, I exactly have these values:

A1: 1+2+3
A2: 2+0.5+10

Is it possible to have sum of these cells (=18.5) in another cell? I tried =SUM(A1:A2), but it returns 0

Improve this question
4
  • I guess those cell input are formatted as text?
    – Terry
    Jul 30, 2013 at 8:14
  • @djerry: yes, but changing it to Number didn't have any effects.
    – kikio
    Jul 30, 2013 at 8:14
  • That's because a "=" is needed in front of it to calculate the cells, but that would mean that you lose you "1+2+3" syntax and that it would show "6" instead. Is it required that "1+2+3" stays visible in A1?
    – Terry
    Jul 30, 2013 at 8:15
  • @djerry: yes. May I want to print this sheet, and I should be able to see the values correctly.
    – kikio
    Jul 30, 2013 at 8:18

2 Answers 2

Reset to default
0

This can't be the most efficient use of this method, but it's what I can figure out. Hopefully someone has something better.

Make sure you have the developer tab in the ribbon. If not, go to File - Options - Customize Ribbon and on the right side place a checkmark in the box for Developer

Now go to the developer tab and on the left you'll see Visual Basic - click that

On the left of your Visual Basic window you will see your VBAProject, right click insert - module and paste the code below into the module

Function Kikio(Rng As String) As Variant
    Kikio= Evaluate(Rng)
End Function

Click Debug - Compile VBAProject and exit the visual basic window

Now on your sheet you can use the function =Kikio() to calculate a sum of one of those cells. Use this in conjunction with =Sum() to sum multiple =Kikio()s in this particular Workbook.

=Sum(Kikio(A1), Kikio(A2))

If you want the =Kikio() function available across all workbooks on the machine, you'll need to put it into your PERSONAL.xlsb file.

Improve this answer
4
  • 1
    Function Kikio(Rng As String) As Variant // Kikio = Evaluate(Rng) // End Function
    – SeanC
    Jul 30, 2013 at 13:40
  • ah string. Thank you @SeanCheshire
    – Raystafarian
    Jul 30, 2013 at 13:45
  • Thanks a lot (your code need a typo correction after Evaluate. a ).)
    – kikio
    Jul 30, 2013 at 14:09
  • Yeah, leftover from my prior (read: inferior) attempt at a solution
    – Raystafarian
    Jul 30, 2013 at 14:16
1

I always prefer non-VBA solutions. There is an undocumented "Evaluate" function that you can use, but I wouldn't recommend using it for very large data sets. Here's how it works.

Under the Formula tab, Pick define name. Name it whatever you want (I used "Result"). And in the "Refers to", type in =EVALUATE(Sheet1!A1)+EVALUATE(Sheet1!A2). you don't have to specify a sheet, but it will do that automatically for you after entering.

Now to use that result, just select a cell and type in "Result" and there you will find the result you want.

If you want to use bigger data sets, VBA might be needed, unless you don't mind the manuel work.

Improve this answer
3
  • Thanks! but, is it possible to define 1 formula, and use it to calculate sum of different cells? For example, "Result" gets cells list as input, and pass it to EVALUATE function. So =Result(A1,A2) means =EVALUATE(Sheet1!A1)+EVALUATE(Sheet1!A2) and =Result(B1,B2) means =EVALUATE(Sheet1!B1)+EVALUATE(Sheet1!B2)
    – kikio
    Jul 30, 2013 at 8:36
  • As far as I know, you can't pass parameters along with a names field. Calling "result" is just calling a name, so I don't think that will work.
    – Terry
    Jul 30, 2013 at 8:43
  • Btw, what I just noticed is that when you pull down results, and you haven't locked the cells (with "$"), the cells change in the formula you used when defining a name. So you might just want to drag your "results" cell to the right and that might calculate the B column too with having to make another formula.
    – Terry
    Jul 30, 2013 at 8:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .