Click here for important information about Computing.net.

Dell / Inspiron 17r

I am trying to use =SUM on a column of numbers that have a letter prefix and a colon, i.e, B:1.2 + B:2.3 + B:2. I get "0" as an answer. I don't know how to write a formula that would eliminate the letter and the colon and have the numbers summed. Thank you.

Brian W

✔ Best Answer

Brian, Try this and see how it works for you:

{=SUM(IFERROR(VALUE(RIGHT(A1:A12,LEN(A1:A12)-2)),0))}

It is an ARRAY formula, so you must use CTRL-SHIFT-ENTER to get the curly brackets.

Also, if these are Totals,

where are you getting them from?

and how are they being entered?

and is the prefix B: actually necessary?

or can they be chopped off before being entered into column A?MIKE

message edited by mmcconaghy

If the expected answer is 5.5, then this works for the example given: =SUM(MID(A1,3,3),MID(A1,FIND(":",A1,3)+1,3),MID(A1,FIND(":",A1,11)+1,3))

(Now tell us that the example you supplied isn't really what you have)

The following might work for more than just the example given, but we won't know until you try it on your real data. It does return 5.5 for your example:

1 - Open a new VBA module.

2 - Paste the following code into the module.

3 - Back in the sheet, enter this "formula" which is actually a User Defined Function, assuming your example is in A1.=SumNumbers(A1,":")

The UDF (User Defined Function):

Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double 'Stolen from: 'https://www.extendoffice.com/documents/excel/2459-excel-sum-cells-with-text-and-numbers.html Dim xNums As Variant, lngNum As Long xNums = Split(rngS, strDelim) For lngNum = LBound(xNums) To UBound(xNums) Step 1 SumNumbers = SumNumbers + Val(xNums(lngNum)) Next lngNum End Function

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Coming in late on this one.

I read your request as looking like:A 1) B:1.2 2) B:2.3 3) B:2with each string in it's own cell.

If that is correct then this ARRAY formula will get you what your looking for:

{=SUM(VALUE(RIGHT(A1:A3,LEN(A1:A3)-2)))}

Since it is an ARRAY formula, you must use CTRL-SHIFT-ENTER to get the

curly brackets.

MIKE

Mike, You are probably right. I missed the word "column".

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

DerbyDad03, Thank you for your response. I'm going to try the suggestion from mmcconaghy.

Thanks again,

Brian W

mmcconaghy, Thank you for your response. It worked correctly when the range was from A1 to A3. When I extended the range for A1 to A12, I got an error message "#Value". Unfortunately, I did not state the question correctly. The sum is based on monthly totals, i.e,, January's total would be in A1 so the sum (in A13) would be just the value in A1; February's total would be in A2 so that sum (in A13) would be the total of the values in A1 and A2. March's total would be in A3 so that sum (in A13) would be the total of the values in A1, A2 and A3. The same pattern would be used for the remaining months.

Thank you for your assistance is appreciated. I do not know how this website is funded; if appropriate, I would like to make a monetary contribution,

Brian W

Always/usually wise(r) or good to try several suggestions as/when posted. Each may offer a viable solution - and each of which may be useful anon...?

OK, Did not know you wanted a running total.

I'll work on it and see what I can come up with.MIKE

re: "Thank you for your assistance is appreciated. I do not know how this website is funded; if appropriate, I would like to make a monetary contribution," It's a little late for that now. Read this:

https://www.computing.net/answers/t...

message edited by DerbyDad03

Brian, Try this and see how it works for you:

{=SUM(IFERROR(VALUE(RIGHT(A1:A12,LEN(A1:A12)-2)),0))}

It is an ARRAY formula, so you must use CTRL-SHIFT-ENTER to get the curly brackets.

Also, if these are Totals,

where are you getting them from?

and how are they being entered?

and is the prefix B: actually necessary?

or can they be chopped off before being entered into column A?MIKE

message edited by mmcconaghy

Mmcconaghy, Thank you for your response. It worked as needed.

Thanks again,

Brian W

message edited by Brian W

~~With my data like:~~A B 1) B:1.2 24.3 2) B:2.3 3) B:2 4) B:1.3 5) B:1.3 6) B:2.4 7) B:3 8) B:1.4 9) B:1.4 10) B:2.5 11) B:4 12) B:1.5And the formula

{=SUM(IFERROR(VALUE(RIGHT(A1:A12,LEN(A1:A12)-2)),0))}

entered as an ARRAY formula using CTRL-SHIFT-ENTER

in cell B1, seems for work for me.

~~Make sure you enter it as an ARRAY formula.~~Gland you got it to work.

MIKE

message edited by mmcconaghy

Mike, Thank you for your assistance over the years that I have used Computing.net. I am greatly disappointed that this great website with extremely knowledgeable moderators will soon come to an end.

Brian W

Click the "Register Password" link to join the following listserv site. Super smart guys. I go there for help when I'm stuck. https://peach.ease.lsoft.com/script...

message edited by DerbyDad03

DerbyDad03, I joined that listserv site. I logged on but could not access the Excel group. How is that done?

Thank you.

Brian W

My bookmark link is: https://peach.ease.lsoft.com/script...

Most of the time I'm already logged in when I click it. Sometimes I need to enter my email address and passwords.

It's been over 15 years since I joined so I don't recall if they sent me a verification email. I do know that every few months (?) I get a "Subscription Probe" email that simply checks to make sure my email address is still active. I never have to respond.

Since I don't know what you see when you log on, I don't have much else to offer.

message edited by DerbyDad03

DerbyDad03, Thank you for your response. I was able to log on to the Listserv.

Brian W

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Yes (14) | ||

No (14) | ||

I don't know (15) |

Discuss in The Lounge

Poll History