Summing a row of cells that may also contain text in the same cell

[Miff]

Dear All (my first query so please excuse any protocol breaches)

1. I am trying to sum a row of 23 cells that each contain a number and sometimes a number with a letter e.g. 2; 4; 17C; 1; 8F (so it should equal 32).
2. The macro formula below that I found works on an individual cell, but I cannot get it to work across a number of cells (BTW I do not know macros as all).
3. I am not wedded to a macro if there is a simpler way - can anyone assist please with a formula?

Many thanks!

Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double
'Updateby Extendoffice
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

 

[johnnyL]

So you want to sum a row of 23 cells, disregarding any text found, just sum the numerical values found in the row?

 

[Miff]

Yes please - for the avoidance of doubt the letters need to remain in the data set, otherwise I would delete them.

 

[Joe4]

Welcome to the Board!

Try this function:

Function SumNum(rng As Range) As Double

    Dim cell As Range
    Dim tot As Double
    
    For Each cell In rng
        tot = tot + Val(cell)
    Next cell
    
    SumNum = tot
    
End Function

Then, if you wanted to sum a range, like A10:E10, you would just use the formula:

=SumNum(A10:E10)

 

[Miff]

Welcome to the Board!

Try this function:

Function SumNum(rng As Range) As Double

    Dim cell As Range
    Dim tot As Double
   
    For Each cell In rng
        tot = tot + Val(cell)
    Next cell
   
    SumNum = tot
   
End Function

Then, if you wanted to sum a range, like A10:E10, you would just use the formula:

=SumNum(A10:E10)

Thank you Joe 4, it works great.

It has prompted a second question to solve. As background the numerical values are sailing race results and the highest (worst) six scores across the 23 races are excluded from the season total. Some of these six worst scores will be say "17F" (as an example) and my formula to recognise the highest scores and deduct them from the total that your macro above creates now doesn't work (as your macro permits me to retain the letters in the cells).

Without pestering you, would you know how to rank the highest to say sixth highest score? Please see snip for representation - far right cell should be an "11" not a "10". I guess I am really asking to have it recognise "17F" as "17" in this example.

 

[Joe4]

It has prompted a second question to solve. As background the numerical values are sailing race results and the highest (worst) six scores across the 23 races are excluded from the season total. Some of these six worst scores will be say "17F" (as an example) and my formula to recognise the highest scores and deduct them from the total that your macro above creates now doesn't work (as your macro permits me to retain the letters in the cells).

Without pestering you, would you know how to rank the highest to say sixth highest score? Please see snip for representation - far right cell should be an "11" not a "10". I guess I am really asking to have it recognise "17F" as "17" in this example.

That is an entirely new, different question, and should be posted in a new thread.

 

[Miff]

That is an entirely new, different question, and should be posted in a new thread.

Understood - cheers

 

[Peter_SSs]

I am not wedded to a macro if there is a simpler way - can anyone assist please with a formula?

For the original question, if the cells with text only ever include a single letter then you could use a standard worksheet formula like this.
The first formula assumes no blank cells in the range.
If there could be blanks then the second formula may be of use to you.

21 05 06.xlsm
	A	B	C	D	E	F
1	2	4	17C	1	8F	32
2	2		17C	1	8F	28
SUMPRODUCT

Cell Formulas
Range	Formula
F1	F1	=SUMPRODUCT(--LEFT(A1:E1,LEN(A1:E1)-ISERROR(RIGHT(A1:E1,1)+0)))
F2	F2	=SUMPRODUCT(--LEFT(0&A2:E2,LEN(0&A2:E2)-ISERROR(RIGHT(0&A2:E2,1)+0)))

 

[sandeep_sarkar]

Dear All

I have a small format -

cc 5

sd 30

sd 20

sdc 10

cc 10

sd 40

I want to sum the names individually : sd, cc, sdc etc like -

sd	90
sdc	10
cc	15
Total:​	115

Please advise, Thanks,

Sandeep

 

[Peter_SSs]

Like this?

22 03 13.xlsm
	A	B	C	D
1	cc 5		sd	90
2	sd 30		sdc	10
3	sd 20		cc	15
4	sdc 10
5	cc 10
6	sd 40
Sum with text

Cell Formulas
Range	Formula
D1:D3	D1	=SUMPRODUCT(--MID(A$1:A$6,FIND(" ",A$1:A$6),20),--(LEFT(A$1:A$6,FIND(" ",A$1:A$6)-1)=C1))