Sum time by month and task

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

EXCEL FORMULA 1. Sum time by month and task

EXCEL

Hard coded formula
Sum time by month and task
Cell reference formula
Sum time by month and task
=SUMIFS($D$5:$D$11,$B$5:$B$11,">="&DATE(YEAR($F5),MONTH($F5),1),$B$5:$B$11,"<"&EOMONTH($F5,0),$C$5:$C$11,"A")
=SUMIFS($D$9:$D$15,$B$9:$B$15,">="&DATE(YEAR($F9),MONTH($F9),1),$B$9:$B$15,"<"&EOMONTH($F9,0),$C$9:$C$15,$C$5)
GENERIC FORMULA

=SUMIFS(time_rng,date_rng,">="&DATE(YEAR(month),MONTH(month),1),date_rng,"<"&EOMONTH(month,0),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.
month: The month 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(month),MONTH(month),1),date_rng,"<"&EOMONTH(month,0),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.
month: The month that you want to sum time by.
task: The task that you want to sum time by.

EXPLANATION

This formula uses the SUMIFS, DATE, YEAR, MONTH and EOMONTH functions to sum the time that is associated with the specific month 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.

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
Convert hours to time How to convert hours into time

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
YEAR Function The Excel YEAR function returns the year from a specified date
MONTH Function The Excel MONTH function returns the month from a specified date