i want to calculate only numbers and ignore date value

[Mishoshow]

Hi Guys,,

i want to calculate only numbers and ignore date value

that the vba code i used it to calculate the number but still calculate the date too

Function SumNum(rng As Range) As Double

Dim cell As Range
Dim tot As Double

For Each cell In rng
tot = tot + Val(cell)
Next cell

SumNum = tot

End Function

 

[Peter_SSs]

Welcome to the MrExcel board!

You haven't said just what you want this function for or where it will be used but as a guess could you use a normal function like this?

=SUMIF(B$1:H$1,"Amount",B2:H2)

 

[Mishoshow]

Welcome to the MrExcel board!

You haven't said just what you want this function for or where it will be used but as a guess could you use a normal function like this?

=SUMIF(B$1:H$1,"Amount",B2:H2)

i want to sum from C2 to H2 and ignore date value at the same time without using B1 to H1 ,, i tried vba code but the result bring a different between the code and the sum Manuel

that's the code i used ,, if u can add something to the code to ignore date i'll be thankful..

Function SumNum(rng As Range) As Double

Dim cell As Range
Dim tot As Double

For Each cell In rng
tot = tot + Val(cell)
Next cell

SumNum = tot

End Function

 

[Peter_SSs]

without using B1 to H1

Can you explain why you don't want to use the heading row?

 

[Mishoshow]

Can you explain why you don't want to use the heading row?

Because i'll use the function or the vba code in many different excel sheets and doesn't have the same heading row.

 

[Peter_SSs]

Then give this UDF a try

Function SumNum(rng As Range) As Double
  Dim cell As Range
  
  For Each cell In rng
    If IsNumeric(cell.Value) And Not IsDate(cell.Value) Then SumNum = SumNum + cell.Value
  Next cell
End Function

 

[Mishoshow]

Then give this UDF a try

Function SumNum(rng As Range) As Double
  Dim cell As Range
 
  For Each cell In rng
    If IsNumeric(cell.Value) And Not IsDate(cell.Value) Then SumNum = SumNum + cell.Value
  Next cell
End Function

Perfect ,, thanks for your time

 

[Peter_SSs]

You're welcome.