Click here for important information about Computing.net.

Solved How to sum numbers with a letter prefix

Dell / Inspiron 17r
January 18, 2021 at 13:11:18
Specs: windows 10, 2.3/8
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


See More: How to sum numbers with a letter prefix


✔ Best Answer
January 20, 2021 at 15:08:27
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

http://www.skeptic.com/

message edited by mmcconaghy



#1
January 18, 2021 at 18:20:15
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


Reply ↓  Report •

#2
January 19, 2021 at 18:12:30
Coming in late on this one.
I read your request as looking like:

     A
1) B:1.2
2) B:2.3
3) B:2 

with 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

http://www.skeptic.com/


Reply ↓  Report •

#3
January 19, 2021 at 19:54:10
Mike,

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

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


Reply ↓  Report •

Related Solutions

#4
January 20, 2021 at 10:04:18
DerbyDad03,

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

Thanks again,
Brian W


Reply ↓  Report •

#5
January 20, 2021 at 10:19:51
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


Reply ↓  Report •

#6
January 20, 2021 at 10:20:29
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...?

Reply ↓  Report •

#7
January 20, 2021 at 13:01:01
OK, Did not know you wanted a running total.
I'll work on it and see what I can come up with.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#8
January 20, 2021 at 14:53:02
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


Reply ↓  Report •

#9
January 20, 2021 at 15:08:27
✔ 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

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#10
January 20, 2021 at 15:48:16
DerbyDad03,

Very disappointed that Computing.net will end.

Brian W


Reply ↓  Report •

#11
January 20, 2021 at 16:29:23
Mmcconaghy,

Thank you for your response. It worked as needed.

Thanks again,
Brian W

message edited by Brian W


Reply ↓  Report •

#12
January 20, 2021 at 16:39:03
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.5  
 

And 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

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#13
January 20, 2021 at 18:45:49
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


Reply ↓  Report •

#14
January 20, 2021 at 19:50:29
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


Reply ↓  Report •

#15
January 22, 2021 at 15:29:26
DerbyDad03,

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

Thank you.
Brian W


Reply ↓  Report •

#16
January 22, 2021 at 15:50:17
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


Reply ↓  Report •

#17
January 23, 2021 at 05:07:29
DerbyDad03,

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

Brian W


Reply ↓  Report •

Ask Question