# Sum only numbers in a range with multiple rows ignoring any text?

#### ynygma

##### New Member
Hello all,

I have been researching this problem across the net and have tried everything from arrays to TRIM with no success.

Here is what I am trying to do (using Excel 2010):

This is just a sample, but I have multiple rows and columns A1:G2, in this example both rows are the range I need.

 Text Text Text Text Text 6 Text 4.5 Text/Text Text Text

<tbody>
</tbody>

I have another cell where I will do my calculation. I want to be able to SUM only the numbers in any range I specify ignoring any Text.

I am intermediate when it comes to excel with basic VBA as well but I am open to any assistance I can get

Regards,
Ynygma

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Ron Coderre

##### MrExcel MVP
This regular formula finds the first space in each of the referenced cells, assuming numbers follow that space, and sums the numbers
=SUMPRODUCT(--(0&MID(A1:G2,FIND(" ",A1:G2&" ")+1,LEN(A1:G2)+2)))

Is that something you can work with?

#### ynygma

##### New Member
This regular formula finds the first space in each of the referenced cells, assuming numbers follow that space, and sums the numbers
=SUMPRODUCT(--(0&MID(A1:G2,FIND(" ",A1:G2&" ")+1,LEN(A1:G2)+2)))

Is that something you can work with?

Yeah I have tried something similar to that and always get #VALUE! for my return value. I just plugged your formula in and same #VALUE! returned. What do you mean by "assuming numbers follow that space"? Sometimes there will be a whole number and sometimes with a decimal

Thank you for assisting, I really appreciate it,
Ynygma

#### perimidt

##### Board Regular
Hello all,

I am intermediate when it comes to excel with basic VBA as well but I am open to any assistance I can get

Hi I am afraid I am i bit rusty with vba, but as far as I can tell this somewhat lenghty functions should work:

Code:
``````Public Function SumOnlyNumbers(Texts As Range)Dim LastSapce As Long
Dim TextLen As Long
Dim CheckIfNumber As Boolean
Dim NumberInCell

SumOnlyNumbers = 0

For Each cell In Texts ' Loops through all the cells
If InStr(1, cell, " ") > 1 Then ' Checks if there are any spaces
TextLen = Len(cell)
For i = 1 To TextLen ' Loops through the text and finds the last space
If Mid(cell, i, 1) = " " Then
LastSpace = i
End If
Next

NumberInCell = Right(cell, TextLen - LastSpace) ' Extracts everything after the last space
CheckIfNumber = IsNumeric(NumberInCell) ' Checks if everything after the last space is numeric

If CheckIfNumber = True Then
SumOnlyNumbers = SumOnlyNumbers + NumberInCell
End If

End If
Next

End Function``````

Note it will only sum numbers that appear at the end of their cells.
For instance would "Text 65 text" not have been summed.

Best regards
Per Erik

Last edited:

#### Ron Coderre

##### MrExcel MVP

The only complication I can think of is leading spaces.
Try this regular formula:
=SUMPRODUCT(--(0&MID(TRIM(A1:G2),FIND(" ",TRIM(A1:G2)&" ")+1,LEN(TRIM(A1:G2))+2)))

Does that work?

#### ynygma

##### New Member
yes and no...

If I plug i in on my example yes, but when I apply it to my actual project I still get he #VALUE!...

The only difference in my sample and actual is I have conditional formatting on all cells and all cells text are centered in the cell.... Will that kill it?

Ynygma

#### Ron Coderre

##### MrExcel MVP

yes and no...

If I plug i in on my example yes, but when I apply it to my actual project I still get he #VALUE!...

The only difference in my sample and actual is I have conditional formatting on all cells and all cells text are centered in the cell.... Will that kill it?

Ynygma

Conditional formatting and cell alignment don't impact the actual cell contents. There's something else going on in the cell values. If they are pasted from a copied website, that may be the issue, since webpages can hold non-displaying characters that aren't space.

#### ynygma

##### New Member
OK, I think I figured out why it is happening on actual. One of my cells has spaces between text, i.e.Red Blue Green. If I remove the spaces it works which I can work with but is there a way to do it even if the cell has spaces between text?

Thx for this regardless !
Ynygma

#### Ron Coderre

##### MrExcel MVP
The key is to find a consistent structure.
For the cells that include numbers, are the numbers ALWAYS after the last space?
If yes... and assuming you are using Excel 2007 or later, this ARRAY FORMULA, completed by holding down the CTRL and SHIFT keys when you press ENTER (instead of just pressing ENTER) sums those numbers
=SUM(IFERROR(--TRIM(RIGHT(SUBSTITUTE(TRIM(A1:G2)," ",REPT(" ",LEN(TRIM(A1:G2)))),LEN(TRIM(A1:G2)))),0))

Does that work?

#### ynygma

##### New Member
Yes that makes total sense and it works in my example perfectly, but not in my actual, because I have merged cells for formatting purposes and arrays don't seem work in merged cells. Kinda sucky limitation, why would merged cells matter because you aren't changing the range or anything...

But I do understand how it all works now better and this has been a tremendous help, array issue aside

Ynygma

Replies
4
Views
114
Replies
7
Views
583
Replies
5
Views
547
Replies
10
Views
344
Replies
4
Views
134

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,194
Messages
5,768,775
Members
425,492
Latest member
blueexcel123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back