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

ynygma

New Member
Joined
Nov 29, 2013
Messages
5
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.

TextTextTextTextText 6Text 4.5Text/Text
TextText

<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.:confused:

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
Joined
Jan 14, 2009
Messages
2,316
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
Joined
Nov 29, 2013
Messages
5
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
Joined
Jan 19, 2012
Messages
132
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
Joined
Jan 14, 2009
Messages
2,316

ADVERTISEMENT

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
Joined
Nov 29, 2013
Messages
5
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
Joined
Jan 14, 2009
Messages
2,316

ADVERTISEMENT

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
Joined
Nov 29, 2013
Messages
5
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
Joined
Jan 14, 2009
Messages
2,316
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
Joined
Nov 29, 2013
Messages
5
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top