< Go Back

Sum by week number

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.
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