Explanation
In this example, the sum range is the named range "time", entered as an Excel time in hh:mm format. The first criteria inside SUMIFS includes dates that are greater than or equal to week date in column F:
date,">="&$F5
The second criteria limits dates to one week from the original date:
date,"<"&$F5+7
The last criteria, restricts data by project, by using the project identifier in row 4:
project,G$4
When this formula is copied across the range G5:H7, the SUMIFS function returns a sum of time by week and project. Notice all three criteria use mixed references to lock rows and columns as needed to allow copying.
Durations over 24 hours
To display time durations over 24 hours use a custom number format with hours in square brackets:
[h]:mm
Related formulas
Sum race time splits
Calculate number of hours between two times
Get work hours between dates
Related functions
SUMIFS Function
The Excel SUMIFS function returns the sum of cells that meet multiple conditions, referred to as criteria. To define criteria, SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be used with cells that contain dates, numbers, and text.
