How to sum by week number

by | Jul 13, 2018 | Excel Tips & Tricks

How to sum by week number

To sum by week number is a bit different than How to sum by month. Instead of finding start and end dates of date range, we focus on exact week number by using WEEKNUM function.

Syntax

=SUMIFS( range of values to sum, range of week number helper column, current week)

Steps

  1. Add a helper column near your table
  2. Type WEEKNUM with actual dates =WEEKNUM(B3)
  3. Go to the result table with week numbers
  4. Start with =SUMIFS(
  5. Select or type the range reference that includes the values that summed $C$3:$C$10,
  6. Continue with the criteria range – criteria pair with week range and week number for the first row $D$3:$D$10,F3
  7. Type ) to close SUMIFS function and press Enter to complete formula

How

The SUMIFS function sums values that meet single or multiple criteria. Instead of filtering dates in a month, we choose to use he WEEKNUM function which simply returns the week number of a specified date. So, the first step is to add a helper column to convert actual dates to week numbers.

=WEEKNUM(B3)

The rest is to use the SUMIFS formula to complete the sum by week operation. You can also use the SUMIF formula because we  only have a single criteria, week number. The SUMIFS' first argument is the values to sum. Other arguments are criteria range – criteria pairs. Because we have single criteria, we use 3 arguments.

=SUMIFS($C$3:$C$10,$D$3:$D$10,F3)

Please pay attention to the absolute references on values and criteria range. They should remain the same as we copy down our formula from week 44 to 47. The criteria argument has a relative reference because we want it to be updated through rows.

Thanks to formatting options of Excel, we can display week numbers with a "Wk" perfix. Adding custom format "Wk 0" to a number adds "Wk" text at front of number. To apply a custom format:

  1. Select the cell to be formatted and press Ctrl+1 to open the Format Cells dialog. An alternative way to do is by right-clicking the cell and then going to Format Cells > Number Tab.
  2. Under Category, select Custom.
  3. Type in the format code into the Type
  4. Finally, click OK to save your changes.

For detailed information on Number Formatting please visit: Number Formatting in Excel – All You Need to Know.