How can I add numbers such as 1.5R + 2.5R?

W

worker

I am trying to add numbers that have alpha characters at the end. I have
tried nesting the SUM and IF functions using FREQUENCY for unique values but,
have been unsuccessful. Any suggestions please.
 
P

Pete_UK

You could use:

=VALUE(LEFT(A2,LEN(A2)-1))

to get the numbers (only) into another column, copy the formula down as
necessary, then sum this column, assuming your data is in column A and
there is only one character after the numerical part.

An alternative is to try wrapping this into an array* formula:

=SUM(VALUE(LEFT(A2:A100,LEN(A2:A100)-1)))

I have assumed a range of A2 to A100 - adjust to suit your data.

* As this is an array formula, then once you have typed it in (or
subsequently edit it), you must use CTRL-SHIFT-ENTER instead of just
ENTER to commit it. If you do this correctly, then Excel will wrap
curly braces { } around the formula when viewed in the formula bar -
you should not type these yourself.

Hope this helps.

Pete
 
Top