Sum time with a vba function

Question

hello, I have

Public Function DataSum(r As Range, Mode As String, col As Long) As Long

          DataSum = Application.SumIf(r.Columns(1), Mode, r.Columns(col))

End Function

it looks at colums(1) for the values Mode (which are text Values) and sum the corresponding values in a chosen column (col).

r in this case is Y68:AI98 and it is a dynamic range.

It works with regular sums, but when it comes to times it throws wrong numbers. I am summing values formatted as [h]:mm:ss.

Summing the below times gives me 1 or, formatted as [h]:mm:ss, 24:00:00 (instead of 15:38:28).  Any ideas?

3:47:04<br><br><br><br>	2:38:14<br><br><br><br>	2:35:46	<br>	---	<br>	1:30:23<br><br><br><br>	2:33:00<br><br><br><br>	2:34:01	<br>	---		<br>	---			<br>	---

Answer 1

Hi,

Your function is returning a LONG variable and times are strored in Excel as decimals formatted to look like the times we recognise. Change the typr declaration of your function to DOUBLE and it will work for times and numbers.

If the sum of the times could exceed 24 hours then format the formula cell as

and if you are summing numbers format as GENERAL

Public Function DataSum(r As Range, Mode As String, col As Long) As Double

           DataSum = Application.SumIf(r.Columns(1), Mode, r.Columns(col))

 End Function

Answer 2

Thanks so much Mike, I tried to work out the problem myself but was out of ideas.