SUMIFS function will be used to demonstrate how to sum if by week number.
Considering the example below, the formula in G5 is:
Figure 1 – Sum if by year
The syntax of SUMIFS function is:
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:
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:
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.
- 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.