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

[ynygma]

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

 

[Ron Coderre]

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]

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]

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:

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

 

[Ron Coderre]

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]

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]

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]

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]

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]

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