Sum values by week

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

EXCEL FORMULA 1. Sum values by week

EXCEL

Hard coded formula
Sum values by week
Cell reference formula
Sum values by week
=SUMIFS($C$5:$C$11,$B$5:$B$11,">=04/03/2019",$B$5:$B$11,"<"&DATE(2019,3,4)+7)
=SUMIFS($C$5:$C$11,$B$5:$B$11,">="&E5,$B$5:$B$11,"<"&E5+7)
GENERIC FORMULA

=SUMIFS(values_rng,date_rng,">=week",date_rng,"<"&DATE(year,month,day)+7)

ARGUMENTS
values_rng: A range that contains the values that are associated with specific date that are to be summed by week.
date_rng: A range that contains the dates that are associated with specific values.
week: The first date of the week 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)

ARGUMENTS
values_rng: A range that contains the values that are associated with specific date that are to be summed by week.
date_rng: A range that contains the dates that are associated with specific values.
week: The first date of the week 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 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 directly entered into the formula or referenced to a specific cell.

RELATED TOPICS

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

RELATED FUNCTIONS

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