We help IT Professionals succeed at work.

# Sumproduct formula for total duration

on
Hi Experts,

I would like to request Experts help create Sumproduct formula to count total hours of specific data (C1 to J1 at "Total" sheet) by comparing with data at "Detail" sheet (Column D).  E.g CBDD at Detail sheet (Column D) are repeated for 8 times, thus total duration (column E- Detail sheet) of CBDD which is 9:45hrs. I have manually copied total duration of CBDD and "Small CC"  at "Total" Sheet for Experts to get better view. Attached the workbook for Experts perusal.
Formula.xls
Comment
Watch Question

## View Solution Only

Commented:
Hi,

Try

=SUMPRODUCT(--(ISNUMBER(SEARCH(C\$1,Detail!\$D\$2:\$D\$49))),--(\$A2<=Detail!\$A\$2:\$A\$49),--(Detail!\$A\$2:\$A\$49<=\$B2),Detail!\$E\$2:\$E\$49)

I think you need to consider the date ranges as well.

Kris

Commented:
Hi Kris,

Thanks for the formula. Anything wrong with the date range?

Commented:

No, I was mentioning that my formula considers dates which I think you didn't mention in the query.

Commented:
Hi,

Yes you're right. I should mention in the Q. Sorry for that. What should I do when the "detail" data rows increased, let say it's touch to 1500 rows (Jan - Dec)?
Commented:
Hi,

In the macro make the adjustments in the last part( if n then..)

If n Then
Dim LastRow As Long
With Worksheets("Detail")
LastRow = .Range("a" & .Rows.Count).End(xlUp).Row
.Range("a" & LastRow + 2).Resize(n, UBound(k, 2)) = k
Union(.Range("b" & LastRow + 2).Resize(n), .Range("e" & LastRow + 2).Resize(n)).NumberFormat = "[h]:mm"
LastRow = LastRow + n
.Range("a2").Resize(LastRow).Name = "Dates"
.Range("d2").Resize(LastRow).Name = "Title"
.Range("e2").Resize(LastRow).Name = "Duration"
End With
End If

and the formula would be

=SUMPRODUCT(--(ISNUMBER(SEARCH(C\$1,Title))),--(\$A2<=Dates),--(Dates<=\$B2),Duration)

Kris

Commented:
Hi Kris,

Thanks a lot for the help.