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:
The second criteria limits dates to one week from the original date:
The last criteria, restricts data by project, by using the project identifier in row 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.
If you need to add up (sum) up race time splits that are some combination of hours, minutes, and seconds, you can simply use the SUM function. The formula in cell H5 is: = SUM ( C5:G5 ) However, you must take care to enter times with the right...
To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary (...
To calculate the total number of work hours between two dates, you can use a formula based on the NETWORKDAYS function, where "start" is the start date, "end" is the end date, "holidays" is a range that includes dates, and "hours" is the number of...
SUMIFS is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values when adjacent cells meet criteria based on dates, numbers, and text. SUMIFS supports logical operators (>,...
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.