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.

## Leave a Comment