Sum time by year and task

This tutorial shows how to sum the time by year and project using an Excel formula, with the SUMIFS and DATE functions

EXCEL FORMULA 1. Sum time by year and task

EXCEL

Hard coded formula
Sum time by year and task
Cell reference formula
Sum time by year and task
=SUMIFS($D$5:$D$11,$B$5:$B$11,">="&DATE(2018,1,1),$B$5:$B$11,"<="&DATE(2018,12,31),$C$5:$C$11,"A")
=SUMIFS($D$9:$D$15,$B$9:$B$15,">="&DATE($F9,1,1),$B$9:$B$15,"<="&DATE($F9,12,31),$C$9:$C$15,$C$5)
GENERIC FORMULA

=SUMIFS(time_rng,date_rng,">="&DATE(year,1,1),date_rng,"<="&DATE(year,12,31),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.
year: The year that you want to sum time by.
task: The task that you want to sum time by.

GENERIC FORMULA

=SUMIFS(time_rng,date_rng,">="&DATE(year,1,1),date_rng,"<="&DATE(year,12,31),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.
year: The year that you want to sum time by.
task: The task that you want to sum time by.

EXPLANATION

This formula uses the SUMIFS and DATE functions to sum the time that is associated with the specific year and project.
Click on either the Hard Coded or Cell Reference button to view the formula that has the year and task name directly entered into the formula or referenced to a specific cell.

Please note that the time and results have the [h]:mm format.

RELATED TOPICS

Related Topic Description Related Topic and Description
Sum time by week and task How to sum the time by week and project
Sum time by month and task How to sum the time by month and project

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
SUMIFS Function The Excel SUMIFS function returns the sum of all numbers in a specified range based on multiple criteria
DATE Function The Excel DATE function returns a date through the use of individual year, month and day parameters