Sum values by week
This tutorial shows how to sum values by week using an Excel formula, with the SUMIFS and DATE functions
|
=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 GENERIC FORMULA
=SUMIFS(values_rng,date_rng,">="&week,date_rng,"<"&week+7)
ARGUMENTS 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 Topic | Description | Related Topic and Description |
|---|---|---|
| Sum time by week and task | How to sum the time by week and project | |
| Sum values by month | How to sum the values by month | |
| Sum values by year | How to sum the values by year |
| 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 |