Function to count numbers in one Cell IE 1+2+10

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having difficulty finding a function to count a series of numbers in a
single cell.

Heres an example of the data, 1+10+4+5+15+1

So the total should be 36 but since the user writes this info in one cell,
and 'sum of that cell' doesn't seem to work, is there something function
wise, simple that I'm missing?

Thanks
 
I'm having difficulty finding a function to count a series of numbers in a
single cell.

Heres an example of the data, 1+10+4+5+15+1

So the total should be 36 but since the user writes this info in one cell,
and 'sum of that cell' doesn't seem to work, is there something function
wise, simple that I'm missing?

Thanks
Click to expand...

You could select the cell, then put an = or + sign in front of the 1 (on the
formula bar).

Or you could use a VBA function or macro to evaluate the contents.
--ron
 
Except we need to keep that one field with all the ones. The only other thing
I can think of is to copy the contents over to the neighbouring field, then
have it sum it.

Just wish I knew a quick way of doing it automatically.
 
Except we need to keep that one field with all the ones. The only other thing
I can think of is to copy the contents over to the neighbouring field, then
have it sum it.

I'm not familiar with running macros. I'll see what I can do, or if you have
a suggestion. I made a macro, but obviously it only takes from H26, and
pastes static info. Is there a way to select the field your beside
(neighbouring field to the left, copy and add = in front of 'whatever it
grabs'?


Range("H26").Select
Selection.Copy
Range("I26").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=" + H26
Range("I27").Select
End Sub
 
Mitch

Ron mentioned using a User Defined Function.


Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function


With 1+10+4+5+15+1 in A1 enter =EvalCell(A1) in B1

With your workbook open hit Alt + F11 to open the VBE.

CTRL + r to open Project Explorer.

Select your workbook/project and Right-click>Insert>Module.

Copy/paste the above Function into that module.


Gord Dibben MS Excel MVP
 
Except we need to keep that one field with all the ones. The only other thing
I can think of is to copy the contents over to the neighbouring field, then
have it sum it.

I'm not familiar with running macros. I'll see what I can do, or if you have
a suggestion. I made a macro, but obviously it only takes from H26, and
pastes static info. Is there a way to select the field your beside
(neighbouring field to the left, copy and add = in front of 'whatever it
grabs'?


Range("H26").Select
Selection.Copy
Range("I26").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=" + H26
Range("I27").Select
End Sub
Click to expand...

Macro would be simply:

==================
Function eval(rg As Range)
eval = Evaluate(rg.Text)
End Function
=====================
--ron
 
Back
Top