Sum by week

The Excel SUMIFS function is used to perform the addition of cells that meet multiple criteria. It can be used to perform addition operation when adjacent cells meet criteria based on numbers, dates or text. The specialty of SUMIFS function is that it can perform the addition of ranges based on several criteria. It can also be used to perform sum by week in Excel.  

Formula

= SUMIFS (value_range, dates_range, “>=” & A1, dates_range, “<” & A1+7)

Explanation

The formula incorporating Excel SUMIFS function to sum by week uses the following arguments:

  • value_range (required): This is the range that needs to be summed.
  • dates_range (required): This is the range that needs to be evaluated.

This works as follows:

  • Here, the SUMIF function evaluates in such a way that it uses two criteria, (I)  dates greater than or equal to the column and (II) dates less than in column plus 7 days.  
  • When this formula starts to work, the Excel SUMIF function generates a sum for every week.  

Example 1

In this example to sum by a week following formula is used using Excel SUMIFS function;

= SUMIFS(C5:C12,B5:B12, “>=” & E5, B5:B12, “<” & E5+7)

This works as follows:

  • Here, the SUMIF function evaluates in such a way that it uses two criteria, (I)  dates greater than or equal to the column E and (II) dates less than in column E plus 7 days.  
  • When this formula starts to work, the Excel SUMIF function generates a sum for every week.
  • All the dates in column E are Mondays. The rest of the Mondays of the week are then calculated by a very simple formula: E5 + 7.

Figure 1. Example of SUMIFS Function to sum by week in Excel

Notes

When you have to summarize data by year, month, quarter, etc, a pivot table can be an amazing solution. This is because they’ll do the grouping without any formulas at all.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar