Sum values by year and task

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

EXCEL FORMULA 1. Sum values by year and task

EXCEL

Hard coded formula
Sum values by year and task
Cell reference formula
Sum values 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(values_rng,date_rng,">=SUMIFS(values_rng,date_rng,">="&DATE(year,1,1),date_rng,"<="&DATE(year,12,31),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.
year: The year 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,1,1),date_rng,"<="&DATE(year,12,31),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.
year: The year 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 year up to the last date of the year by using the DATE function with the input year of the one that you are summing by. The day and month inputs in the DATE function are 1 (day) and 1 (month) to represent the first date of the year and 31 (day) and 12 (month) to represent the last date of the year.

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 specific cells.

RELATED TOPICS

Related Topic Description Related Topic and Description
Sum time by year and task How to sum the time by year and project
Sum values by year How to sum the values by year
Sum values by week and task How to sum values by week and task
Sum values by month and task How to sum values by month 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