Exceljet

Quick, clean, and to the point

Sum time by week and project

Excel formula: Sum time by week and project
Generic formula 
=SUMIFS(time,date,">="&A1,date,"<"&A1+7,project,"A")
Explanation 

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.

How this formula works

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

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.