We help IT Professionals succeed at work.

Sumproduct formula for total duration

Cartillo
Cartillo asked
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

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

Author

Commented:
Hi Kris,

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

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

Author

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)?  
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

Author

Commented:
Hi Kris,

Thanks a lot for the help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.