Trying to sum time values

[rogerte]

I have a worksheet that contains three columns
Columns A and B contain identifier strings, and column C contains a time value in the format hh:mm

I am trying to write a function that stores, on a separate summary sheet, the total time where columns A and B contain specific data (e.g. if there are 2 entries where A = "xxx" and B = "yyy" and the time entries are "11:55" and "13:15" the result should be "25:10", so I have used the numberformat as below)

I have this in my main function:

 Worksheets("Summary").Range("C" & j).Value = addtime(sF, sS)
      Worksheets("Summary").Range("C" & j).NumberFormat = "[h]:mm"

and this is the function:

Function calctime(sF, sS) As Double
Dim i As Integer, l As Integer
Dim tot As Double

tot = 0
l = FindLastRow("Sheet1")
For i = 2 To l
  If (Worksheets("Sheet1").Range("A" & i).Value = sF) And (Worksheets("Sheet1").Range("B" & i).Value = sS) Then
    tot = tot + Worksheets("Sheet1").Range("C" & i).Value
  End If
Next
addtime = tot
End Function

I have tried several variations of this but it always returns 0:00.

Can anyone kindly point out my errors?

Many thanks

 

[SkipVought]

Hi,

First we need to clarify that your time values in column C are Durations and not times of day.

I’m assuming the former.

Second, you do not need any VBA. This can and should be done on the sheet with Excel functions. Since you gave scant information, this is a scant solution:
[tt]
=SUMPRODUCT((a=“xxx”)*(b=“yyy”)*(c))
[/tt]
...where a is the range reference in column A, b is the range reference in column B and c is the range reference in column C.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[rogerte]

Thanks, but unfortunately I can't get this to work.

Sheet1 has the format:

A = code1, B= code2, C = ElapsedTime (E-D hh:mm) D = StartTime (hh:mm) , E = FinishTime (hh:mm), F = Date (dd/mm/yyyy)

The StartTime and EndTime are added by the VBA formula Now - (Int(Now)) so do not contain the date portion of the value

Monthly there are about 2500 different rows with up to 96 combinations of sF and sS.

can be summarised as totaltime = sum of items in Column C for each combination of A and B

Summary sheet has the format
A = code1, B= code2, C = TotalTime ([h]:mm)

EDIT: Have found I can use the SUMIFS function and so have overcome the problem - thanks for your previous reply

 

[SkipVought]

SUMPRODUCT() is similar to SUMIFS() and COUNTIFS(), but IMHO,it is much more intuitively simple to use.

Also, I would have used Named Ranges or Structured Tables which makes formulas nearly self documenting and makes spreadsheet maintenance much more maintainable.

Named Ranges on Sheet 1 via Formulas > Defined Names > Create Names from Selection > Create Names from Selection in TOP ROW using your headings:

So your formula on Sheet2 would look like (notice the three nested sets of parentheses within the function parentheses)...
[tt]
C2: =SUMPRODUCT((code1=A2)*(code2=B2)*(ElapsedTime))
[/tt]

Also, Splitting the Date from the Start & Finish Times is a bit awkward. StartTime and FinishTime ought to have the Date component to work smoothly. Are there no instances EVER where the Start and Finish are not in the same day? I would think not. Duration will work just as well if not better, if you have spanning days.


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

...also...

can be summarised as totaltime = sum of items in Column C for each combination of A and B

Have you considered using a Pivot Table?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}