Sum values by week and task

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

EXCEL FORMULA 1. Sum values by week and task

EXCEL

Hard coded formula
Sum values by week and task
Cell reference formula
Sum values by week and task
=SUMIFS($D$5:$D$11,$B$5:$B$11,">=04/03/2019",$B$5:$B$11,"<"&DATE(2019,3,4)+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(values_rng,date_rng,">=week",date_rng,"<"&DATE(year,month,day)+7,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.
week: The first date of the week that you want to sum the values and tasks by.
task: The task that you want to sum values by.
year: The year of the week that you are summing by.
month: The month of the week that you are summing by.
day: The first day of the week that you are summing by.

GENERIC FORMULA

=SUMIFS(values_rng,date_rng,">="&week,date_rng,"<"&week+7,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.
week: The first date of the week that you want to sum the values and tasks by.
task: The task that you want to sum values by.

EXPLANATION

The formula for the hard coded and cell reference approaches is slightly different, however, still applies the same concept. The formula sums the values by each task that are associated with the dates that are greater than or equal to the start of the week, by using the first date of the week, up to the last date of the week by simply adding 7 to the first date of the week. Therefore, you only need to use one date (first date of the week) to sum the values that are associated with a specific week.

Click on either the Hard Coded or Cell Reference button to view the formula that has the first date of the week 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 week and task How to sum the time by week and project
Sum values by week How to sum values by week
Sum values by month How to sum the values by month

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