SUMIFS function will be used to demonstrate how to sum if by week number.

**Generic Formula**

**=SUMIFS(sum_range,week_range,week_number)**

**Example**

Considering the example below, the formula in G5 is:

**=SUMIFS($C$3:$C$9,$D$3:$D$9,F5)**

* Figure 1 – Sum if by year*

**Explanation**

The syntax of SUMIFS function is:

**=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)**

In this case, sum_range is C3:C9, and criteria_range (week_range) is D3:D9.

Column D is used to get the week number in a month.

The formula used to get the week number in a month is:

**=INT((DAY(B3)+6)/7)**

The criterion that is used in the formula is **D3:D9,F5. **Cells in range B3:B9 is added if its corresponded week number in column D matches the week number in column F.

You can adapt this formula and combine more criteria for SUMIFS function. Just like the example below

*Figure 2 – Sum if by year*

In this case, the formula in H5 is:

**=SUMIFS($D$3:$D$9,$E$3:$E$9,$G5,$C$3:$C$9,H$4)**

Criterion 1: **E3:E9,G5** which matches the week number. **$G5 **means that column G is fixed when you extend the formula through column H to J.

Criterion 2: **E3:E9,H4 **which matches the purpose value. **H$4 **means that row 4 is fixed when you extend the formula through row 5 to 8.

**Notes**

- Remember to press F4 after the sum_range and week_range to change them into a fixed array before extending the formula.
- If you want to get the week number in a year, use WEEKNUM(date) function instead.

## Leave a Comment