Sum of numbers next to specific letters in a string

[alexthecamel]

Hi All,

Am a little embarased that I can't resolve this one, have searched here and all over the internets to no avail.

I want to do a sum from a column where it only adds numbers that are directly preceded by a specific letter. eg;

S3
S4
S1

Would return 8 (or even S8 would do)
Not sure if I am over thinking and there is a simple solution to this one.

Many Thanks

 

[Phanmore]

This forrmula could probably be shorter, but it works if your looking for a quick solution:

=SUM((RIGHT(A1:A100,LEN(A1:A100)-1))+0)

Confirm with Ctrl + shift + enter

 

[Sektor]

[COLOR="Blue"]Function[/COLOR] SumAll([COLOR="Blue"]ParamArray[/COLOR] arr() [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] result [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Double[/COLOR], i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], rng [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "\d+"
        [COLOR="Blue"]For[/COLOR] i = [COLOR="Blue"]LBound[/COLOR](arr) [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arr)
            [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] rng [COLOR="Blue"]In[/COLOR] arr(i)
                [COLOR="Blue"]If[/COLOR] .Test(rng) [COLOR="Blue"]Then[/COLOR]
                result = result + .Execute(rng)(0)
            [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
            [COLOR="Blue"]Next[/COLOR]
        [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    SumAll = result
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Sektor]

Here's formula alternative. Adjusting range is mandatory. Press Ctrl+Shift+Enter):

@SUM(SUBSTITUTE(A1:A100;"S";"")+0)
or
@SUM(--SUBSTITUTE(A1:A100;"S";""))
or
@SUM(SUBSTITUTE(A1:A100;"S";"")*1)

 

[alexthecamel]

Thank you both for your replies. Much appreciated.

As ever I guess I wasn't specific enough.

Phanmore: Your solution works exactly as I requested it. I should have pointed out however, the cells could also contain D3 for example which I want to add seperately.

Sektor: I would prefer a formula based solution, and your suggestions look viable as I could substitute the S for a D or whatever, but I'm unable to enter them (with or without Ctrl-Shift-Enter, or substituting the @ for = )

I'm using Excel 2003 if that matters.

Thanks again to you both for taking the time to reply.

 

[Phanmore]

This may be a better solution:

=SUMPRODUCT(--(LEFT(A1:A100,1)="D"),(RIGHT(A1:A100,LEN(A1:A100)-1)+0))

This will only sum fields with D, you can change the letter to S, or any other letter.

 

[Sektor]

Sorry, forgot to change delimiter (";" to ","). This formula will eliminate the need of contiguous range.

For Excel 2007/2010:
=SUM(IFERROR(--SUBSTITUTE(A1:A4,"S",""),0))

For Excel 2003:
=SUM(IF(ISERROR(--SUBSTITUTE(A1:A4;"S";""));0;--SUBSTITUTE(A1:A4;"S";"")))