Sum time by week and task

This tutorial shows how to sum the time by week and project using an Excel formula, with the SUMIFS function

Excel

Sum time by week and task using SUMIFS function

EXCEL FORMULA 1. Sum time by week and task

EXCEL

Hard coded formula

Cell reference formula

=SUMIFS($D$5:$D$11,$B$5:$B$11,">="&$F5,$B$5:$B$11,"<"&$F5+7,$C$5:$C$11,"A")

=SUMIFS($D$9:$D$15,$B$9:$B$15,">="&$F9,$B$9:$B$15,"<"&$F9+7,$C$9:$C$15,$C$5)

GENERIC FORMULA

=SUMIFS(time_rng,date_rng,">="&week,date_rng,"<"&week+7,task_rng,task)

ARGUMENTS
time_rng: A range that contains the times that are associated with specific task and date.
date_rng: A range that contains the dates that are associated with specific task and time.
task_rng: A range that contains the task names that are associated with the specific date and time.
week: The week that you want to sum time by.
task: The task that you want to sum time by.

GENERIC FORMULA

=SUMIFS(time_rng,date_rng,">="&week,date_rng,"<"&week+7,task_rng,task)

EXPLANATION

This formula uses the SUMIFS function to sum the time that is associated with the specific week and project.

Click on either the Hard Coded or Cell Reference button to view the formula that has the task name directly entered into the formula or referenced to a specific cell.

In this example the formula sums the time that is associated with the relevant task and week through the use of the SUMIFS function. Please note that the time and results have the [h]:mm format.