Sum #'s in a cell that has text AND numbers?
 

I don't know if this is possible, but thought it was worth asking. Is there
a function in excel where it will add the numbers in a cell based on the text
that's in it? For example, here's what I want to do:

A1: S=8.0
A2: (blank)
A3: V=8.0
A4: S=4.0

Based on the cells listed above, what formula can I use to get the following?

S=12.0
V=8.0

Thanks in advance!

Sum #'s in a cell that has text AND numbers?
 

Try these:

Both are array formulas. Entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=SUM(IF(LEFT(A1:A4)="S",MID(A1:A4,3,10)+0))

=SUM(IF(LEFT(A1:A4)="V",MID(A1:A4,3,10)+0))

Biff

"Shirley" wrote in message
...
I don't know if this is possible, but thought it was worth asking. Is
there
a function in excel where it will add the numbers in a cell based on the
text
that's in it? For example, here's what I want to do:

A1: S=8.0
A2: (blank)
A3: V=8.0
A4: S=4.0

Based on the cells listed above, what formula can I use to get the
following?

S=12.0
V=8.0

Thanks in advance!

Sum #'s in a cell that has text AND numbers?
 

Data Text to Columns select Delimited Next select Other then enter =
in the square box Finish
Then =SUM(B1:B4)


"Shirley" wrote:

I don't know if this is possible, but thought it was worth asking. Is there
a function in excel where it will add the numbers in a cell based on the text
that's in it? For example, here's what I want to do:

A1: S=8.0
A2: (blank)
A3: V=8.0
A4: S=4.0

Based on the cells listed above, what formula can I use to get the following?

S=12.0
V=8.0

Thanks in advance!

Sum #'s in a cell that has text AND numbers?
 

Pretty sure there isn't a function, but you could get the result in a
few steps.
Enter =IF(SEARCH("S=",A1)=1,MID(A1,3,9),"") into B1, copy the formula
down, then highlight the whole of column B, copy, then paste special
values, then hit DataText to ColumnsNextFinish. The sum of column B
will then be the sum of S. Same thing for V
Not sure if you already thought of this, but it's one way to get your
answer.

Tony

On Dec 22, 10:03 am, Shirley
wrote:
I don't know if this is possible, but thought it was worth asking. Is there
a function in excel where it will add the numbers in a cell based on the text
that's in it? For example, here's what I want to do:

A1: S=8.0
A2: (blank)
A3: V=8.0
A4: S=4.0

Based on the cells listed above, what formula can I use to get the following?

S=12.0
V=8.0

Thanks in advance!