You could use this UDF with a formula like
=SUM(FirstValue(A1:D1))
Code:Function FirstValue(inRange As Range) As Variant Dim Result As Variant Dim i As Long, j As Long With inRange If .Cells.Count = 1 Then Result = Val(CStr(.Cells(1, 1).Value)) Else Result = .Value For i = 1 To .Rows.Count For j = 1 To .Columns.Count Result(i, j) = Val(CStr(Result(i, j))) Next j Next i End If End With FirstValue = Result End Function
This approach will handle data that has varying lengths of leading numerals.
How to sum only with the specific character ?
for example ...
1000G. 1000M. 1000g 2000e. = i want to sum only cell having g or G = 2000
Control+shift+enter, not just enter:
=SUM(IF(1-ISNUMBER(A2:D2),IF(ISNUMBER(SEARCH("G",A2:D2)),LEFT(A2:D2,SEARCH("G",A2:D2)-1)+0)))
Thank you its working perfect.... great...
Now tell me how to count cell that contains specific characters
for example
1000d. 2000f 3000g 4000d 3000d. =count D answer 3
You could use this UDF with a formula like
=SUM(FirstValue(A1:D1))
Code:Function FirstValue(inRange As Range) As Variant Dim Result As Variant Dim i As Long, j As Long With inRange If .Cells.Count = 1 Then Result = Val(CStr(.Cells(1, 1).Value)) Else Result = .Value For i = 1 To .Rows.Count For j = 1 To .Columns.Count Result(i, j) = Val(CStr(Result(i, j))) Next j Next i End If End With FirstValue = Result End Function
This approach will handle data that has varying lengths of leading numerals.
I think the following UDF will give you the values you want...
Function SumRange(Rng As Range) As Double
Dim Cell As Range
For Each Cell In Rng
SumRange = SumRange + Val(Cell.Value)
Next
End Function