Sum values while ignoring text in same calls

[freebird11]

Hello,

I would appreciate your help with the following conundrum:

I want to add the numbers from a set of cells that contain both numbers and text. Below is the example:

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]11 BUL[/TD]
[/TR]
[TR]
[TD]55 CAN; 81 ITA; 3 BUL[/TD]
[/TR]
[TR]
[TD]BUL[/TD]
[/TR]
[TR]
[TD]71 CAN; 21 POL[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ITA[/TD]
[/TR]
</tbody>[/TABLE]











So I want to create a function that would take the value of the first cell (11) and add it to the sum of the values of the second cell (139), and so on for all the cells. At the end I should get one big fat number.

The trick is that cells have different amounts of values, some cells have no values or text, and some only have text.

Anyone have an ingenious solution?

Thanks!

 

[Ron Coderre]

With your sample data in A1:A6

AND, as long as your structure remains constant...up-to-two-digits...a space...three-letters...semicolon
this ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER) returns a total of the numbers

B1: =SUM(IFERROR(--(0&TRIM(MID(A1:A10,{1,9,17,25,33,41},{2;2;2;2;2;2}))),0))

For your data, that formula returns: 242

Is that something you can work with?

 

[freebird11]

Thanks for the quick reply Ron.

That line of code is really cool and works for most of the cells. However, I've found that it doesn't work for the whole set (107 cells).

I'm trying to narrow down the ones it doesn't work for.

Example: when it looks at

H9: 2 CAN; 2 POL

the function returns 12. If I change it to

H9: 02 CAN; 02 POL

it returns 24.

When I use the function for the entire set, it returns 29, the value of the first cell.

 

[mas123]

You can use this function

Function SumNumOnly(rngS As Range, Optional strDelim As String = " ") As Double    Dim xNums As Variant, lngNum As Long
    xNums = Split(rngS, strDelim)
    For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumOnly = SumNumOnly + Val(xNums(lngNum))
    Next lngNum
End Function

you can call it as this:

=SumNumOnly(a1)

 

[Ron Coderre]

Try this ARRAY FORMULA variation.

=SUM(IFERROR(--MID(0&A1:A6&REPT(" ",50),IF(MID(0&A1:A6&";"&REPT(" ",50),COLUMN($A:$AN),1)=";",COLUMN($A:$AN)-6),2),0))

• Copy that formula
• Paste it into the formula bar
• Hold down CTRL and SHIFT when you press ENTER

Does that help?

 

[freebird11]

Ron,

Thanks, that formula seemed to do the trick.

I tried using the custom function, inserting module into the worksheet etc., but wasn't able to try it out. Don't know why.

Thanks again!