How to count by week number

by | Jul 13, 2018 | Excel Tips & Tricks

How to count by week number

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

Syntax

=COUNTIFS( range of week number helper column, current week number)

Steps

  1. Add a helper column near your table.
  2. On the helper column, use the WEEKNUM function with actual dates =WEEKNUM(B3)
  3. Go to results table with week numbers
  4. Start with =COUNTIFS(
  5. Continue with criteria range – criteria pair with week range and week number for first row (i.e. $D$3:$D$10,F3)
  6. Type in ) to close the COUNTIFS function and press Enter to complete the formula

How

The COUNTIFS function counts the values that meet any number of criteria. Instead of filtering dates in a month, we get help from the 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)

From here on, all we need to do is a COUNTIFS calculation (or even COUNTIF in this case, because we have only single criteria, week number). The COUNTIFS arguments are 'criteria range' – 'criteria' pairs. Because we have a single criteria, we're going to use 2 arguments.

=COUNTIFS($D$3:$D$10,F3)

Please pay attention to 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 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 about Number Formatting please see: Number Formatting in Excel – All You Need to Know