## Summary

To sum up hours by week and project, you can use the SUMIFS function. In the example shown, the formula in G5 is:

``````=SUMIFS(time,date,">="&\$F5,date,"<"&\$F5+7,project,G\$4)
``````

where "time" (D5:D15), "date" (B5:B15), and "project" (C5:C15) are named ranges.

## Generic formula

``=SUMIFS(time,date,">="&A1,date,"<"&A1+7,project,"A")``

## 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
`````` Author ### Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.