Sum values by month and task

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

EXCEL FORMULA 1. Sum values by month and task

EXCEL

Hard coded formula
Sum values by month and task
Cell reference formula
Sum values by month and task
=SUMIFS($D$5:$D$11,$B$5:$B$11,">="&DATE(YEAR("01/03/2019"),MONTH("01/03/2019"),1),$B$5:$B$11,"<"&EOMONTH("01/03/2019",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(values_rng,date_rng,">="&DATE(YEAR(date),MONTH(date),1),date_rng,"<"&EOMONTH(date,0),task_rng,task)

ARGUMENTS
values_rng: A range that contains the values that are associated with specific task and date.
date_rng: A range that contains the dates that associated with specific task and value.
task_rng: A range that contains the task names that are associated with the specific date and value.
date: The fist date of the month that you want to sum the values and tasks by.
task: The task that you want to sum values by.

GENERIC FORMULA

=SUMIFS(values_rng,date_rng,">="&DATE(YEAR(date),MONTH(date),1),date_rng,"<"&EOMONTH(date,0),task_rng,task)

ARGUMENTS
values_rng: A range that contains the values that are associated with specific task and date.
date_rng: A range that contains the dates that associated with specific task and value.
task_rng: A range that contains the task names that are associated with the specific date and value.
date: The fist date of the month that you want to sum the values and tasks by.
task: The task that you want to sum values by.

EXPLANATION

This formula sums the values by each task that are associated with the dates that are greater than or equal to the first date of the month up to the last date of the month by using the EOMONTH function with the first date of the month used as the input. Therefore, you only need to use one date (first date of the month) to sum the values that are associated with a specific month.

Click on either the Hard Coded or Cell Reference button to view the formula that has the first date of the month and the task name directly entered into the formula or referenced to specific cells.

RELATED TOPICS

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

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