How to Sum by Week Number in Excel

In statistic report, we often statistic data for a certain date range. Sometimes we can use an exact week number instead of entering a start date and an end date as a period to statistic data.

For example, to sum data by week 1, that means we only need to calculate data belongs to week 1 other than a fixed date range with start date and end date. In this case, if week number is not provided in original data table, we need to use WEEKNUM function to get week number for entered date firstly, then we can use a formula contains SUMIFS function to sum data by this week properly. This tutorial will show you ‘sum by week number’ with simple descriptions, screenshots and explanations, and we will let you know how WEEKNUM and SUMIFS functions work, how to set parameters in the formula, you can learn sum data per your requirement clearly by yourself, and finally, you can work well with these functions in your daily work.

EXAMPLE:

FORMULA:

As we need to sum data by week number, first of all, we need to get week number for all entered data in ‘Date’ list. So, in C2, enter the formula =WEEKNUM(A2).

After clicking Enter, we get ‘1’ as the formula result.

As we enter ‘Week 1’ as criteria in ‘Week No.’ column, so we need to display ‘Week + No.’ in ‘Week Number’ column as well, otherwise we cannot apply formula properly. Update previous formula to =”Week”&” “&WEEKNUM(A2). Then we get ‘Week 1’ properly.

Drag down the cell to copy the formula. Then we can get all week numbers for entered dates.

Now, we have all week numbers, we can apply SUMIFS function to sum data per week number criteria properly. In F1 enter the formula =SUMIFS($B$2:$B$11,$C$2:$C$11,E2). After clicking Enter, we can get correct total amount 1000.

HOW THIS FORMULA WORKS:

Firstly, we applied WEEKNUM function in this instance. WEEKNUM function can returns week number for the entered date. You can enter a date within double quote in the function. For example, enter =WEEKNUM(“10/15/2020”), click Enter, we will get 42. That means 10/15/2020 belongs to week 42 in this year.

We can also select a cell number directly when applying this function. In this instance, we can get correct week number after applying the formula.

Then we use SUMIFS function =SUMIFS($B$2:$B$11,$C$2:$C$11,E2) to sum data. For SUMIFS function, it sum data with multiple criteria, the syntax is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

In this case, $B$2:$B$11 is the sum range, it provides values to sum, as it is an absolute range in this case, so we add $ before row and column number to lock the range, so when copying formula to other cells, this range is fixed, user doesn’t need to adjust the sum range any more.

$C$2:$C$11 is the criteria range, it provides all week numbers, it is an absolute range in this case as well, so we add $ before row and column number; as we want to find out week number from this range matches to week 1, so we must find out all week 1 in ‘Week Number’ column, above all, the last parameter in the formula is E2: Week 1.

RESULT:

Let’s check if the formula works well for week 1 and  week 2.

EXAMPLE 2:

This instance is a bit more complex then former instance. See screenshot below:

We add ‘Product’ into original data table and destination table. In above screenshot we can see that week numbers are already calculated properly by WEEKNUM function refer to former instance. In this instance, we just need to update formula which is based on SUMIFS function.

FORMULA:

In G1 enter the formula =SUMIFS($C$2:$C$11,$D$2:$D$11,G$1,$A$2:$A$11,$F2).

HOW THIS FORMULA WORKS:

Compare with former formula =SUMIFS($B$2:$B$11,$C$2:$C$11,E2), SUMIFS function has two criteria ranges and two criteria.

The first criteria range: $D$2:$D$11 (Week Number), the matching criteria is G$1 (Week 1);

The second criteria range: $A$2:$A$11 (Product), the matching criteria is $F2 (C4003).

There is no priority for the two groups of criteria range and criteria, you can change the order if you like, formula =SUMIFS($C$2:$C$11,$A$2:$A$11,$F2,$D$2:$D$11,G$1) also works.

Unlike the criteria ‘E2’ in example 1 (when copying the formula to next cell, E2 is updated to E3 automatically), we add $ in criteria ‘G$1’ before ‘1’ to lock row number because week1 is in G1 and week2 is in H1, the row number is fixed, so row number should be kept in formula and only column can be updated automatically when copying the formula to other cells. For example, if $ is not added, when applying the formula for cell G2, the criteria will be changed from G1 to G2, the criteria is not ‘Week 1’ anymore, the formula is executed improperly.

Besides, we also add $ before ‘F’ in criteria ‘$F2’ to lock ‘F’ column with similar reason, so even we copy the formula to H2, the criteria is still F2 in the formula and will not be changed to G2 in the case that $ is not added.

RESULT:

Let’s see if this formula works correctly. Obviously, it works well.

NOTES:

If you are confused of the sum range and criteria range in the formula, you can also name range firstly, for example

$C$2:$C$11=Amount;

$D$2:$D$11=WeekNumber;

$A$2:$A$11=Product.

To name range, just select the whole range (for example A2:A11, ignore the header), and enter name in name box (for example ‘Product’). Please be aware that there should be NO space in name.

Then update formula to =SUMIFS(Amount,Product,$F2,WeekNumber,G$1). It looks clearly now.

Check the result for the formula. It works well.

Related Functions


  • Excel WEEKNUM function
    The Excel WEEKNUM function returns the week number of a specific date, and the returned value is ranging from 1 to 53.The syntax of the WEEKNUM function is as below:=WEEKNUM (serial_number,[return_type])…
  • Excel SUMIFS Function
    The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…

How to Calculate Weekly Average by Formula in Excel

In our daily life we often create tables for recording the money we spend for lunch or dinner or something else per date. This can help us know the total cost or average cost per date or other unit clearly via some statistic related functions in excel, and then we can cut back on unnecessary expenses and save our money.

Normally, one method for statistic in excel is creating pivot table. we can create a pivot table to auto calculate the total cost or average cost base on the values recorded in original table, and we can do filter by date or month or quarter to show different average cost for different period. Details see below screenshot.

Calculate Weekly Average 1

Obviously, Week is not listed in default grouping date list.

If you want to know the method for counting average data by week (without using pivot table), you can read below article, it can help you to solve your question.

Calculate Weekly Average by Formula in Excel


In Pre-Condition:

Prepare a simple table for demonstration. See screenshot below. You can see that we spend 10 or 5 or other values for lunch on different dates, some dates are included in one week. We want to count the weekly average for spend.

Calculate Weekly Average 2

Step 1: Create another table with three columns Week No., Year and Average. This table is used for recording the week number, year and the weekly average for launch spend.

Calculate Weekly Average 3

Step 2: In E2, enter the formula =WEEKNUM(C2,2).

Calculate Weekly Average 4

For function WEEKNUM, we set the second parameter as 2, that means week begins on Monday.

Calculate Weekly Average 5

Step 3: Click Enter to get returned value. We get 2 in this case. That means the date belongs to the second week in year 2019.

Calculate Weekly Average 6

Step 4: In F2, enter the formula =Year(C2). We can get Year 2019.

Calculate Weekly Average 7

Step 5: In G2, enter the formula =IF(AND(E2=E1,F2=F1),””,AVERAGEIFS($B$2:$B$10,$E$2:$E$10,E2,$F$2:$F$10,F2)). Then we get the returned value 10.

Calculate Weekly Average 8

Comment: In this formula, we use AVERAGEIFS function to calculate the average by specific criteria, and use IF function to classify dates by week and then get according average. If (E2=E1,F2=F1) is true, then we get blank value, otherwise we will get the weekly average.

Step 6: Drag the fill handle to fill the following cells in the table. We get weekly average values for all weeks.

Calculate Weekly Average 9

Comment: If you want to ignore Year, you can just enter the formula =AVERAGEIFS($B$2:$B$10,$E$2:$E$10,E2). Then we can get below table. You can depend on your requirement to determine whether add or remove criteria.

Calculate Weekly Average 10

Related Functions


  • Excel WEEKNUM function
    The Excel WEEKNUM function returns the week number of a specific date, and the returned value is ranging from 1 to 53.The syntax of the WEEKNUM function is as below:=WEEKNUM (serial_number,[return_type])…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel AND function
    The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…)…

 

 

Converting Week Number to Date

This post will guide you how to convert a week number to a date with a formula in Excel. How do I calculate a date from a week number and a year with formula in Excel. Or how to get week number from a given date with formula in Excel.

Convert Week Number to Date


Assuming that you have a week number and a year number in your worksheet, and you want to get the start date and end date in that give week in that year. How to achieve it.

You need to create a formula based on the MAX function, the DATE function and the WEEKDAY function to convert week number to a date in Excel. Just do the following steps:

#1 Type the year number in Cell C1, and type the week number in Cell C2.

#2 Type this formula into the formula box of the Cell C3, then press Enter key in your keyboard. The Serial number of the start date is calculated in Cell C3.

=MAX(DATE(C1,1,1),DATE(C1,1,1)-WEEKDAY(DATE(C1,1,1),2)+(C2-1)*7+1)

convert week to date1

#3 Type the following formula into the formula box of the Cell C4, and then press Enter key in your keyboard. And the serial number of the end date is calculated in Cell C4.

=MIN(DATE(C1+1,1,0),DATE(C1,1,1)-WEEKDAY(DATE(C1,1,1),2)+C2*7)

convert week to date2

#4 select the cells C3:C4 and then go to HOME tab, click Number format list box, and select Short Date item. The serial number are changed to a standard date format.  Or you can also select Long Date format from the drop-down list of the Number format.

convert week to date3

Get Week Number From a Date


If you want to get the week number from a given date, then you can use the WEEKNUM function to quickly achieve it. Just type the formula into the Cell D3:

=WEEKNUM(C3,1)

convert week to date4

Related Functions


  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
  • Excel MAX function
    The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • Excel WEEKDAY function
    The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…
  • Excel WEEKNUM function
    The Excel WEEKNUM function returns the week number of a specific date, and the returned value is ranging from 1 to 53.The syntax of the WEEKNUM function is as below:=WEEKNUM (serial_number,[return_type])…

 

Excel WEEKNUM function

This post will guide you how to use Excel WEEKNUM function with syntax and examples in Microsoft excel.

Description

The Excel WEEKNUM function returns the week number of a specific date, and the returned value is ranging from 1 to 53. And this function will start to count the week number from January 1 and the Sunday is the first day in one week by default.

The WEEKNUM function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.

The WEEKNUM function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the WEEKNUM function is as below:

=WEEKNUM (serial_number,[return_type])

Where the WEEKNUM function arguments is:

  • Serial_number –This is a required argument. An Excel date that you want to get the week number. The dates should be typed by the DATE function to make sure it is a valid Excel date. For example, use DATE(2018,3,20) to pass into the WEEKNUM function and then it will return the week number.
  • return_type – This is an optional argument. A number that determines the first day of the week for the year, the default value is 1.
Return_type Week begins on System
1 or omitted Sunday 1
2 Monday 1
11 Monday 1
12 Tuesday 1
13 Wednesday 1
14 Thursday 1
15 Friday 1
16 Saturday 1
17 Sunday 1
21 Monday 2

Note:

  • If serial_number is out of range for the current date base value, a #NUM! Error is returned.
  • If return_type is out of the range specified in the table above, a #NUM! Error is returned.
  • A serial date is how the Microsoft Excel stores dates and it represents the number of days since 1900-01-01, so the January 1, 1900 date is serial number 1 by default.

Excel WEEKNUM Function Examples

The below examples will show you how to use Excel WEEKNUM Function to return the week number from an Excel date.

1# get number of the week from a date value 2018/03/20, enter the following formula in Cell B1.

=WEEKNUM(DATE(2018,3,2018))

excel weeknum examples1


Related Functions

  • Excel WEEKDAY function
    The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…
  • Excel WORKDAY function
    The Excel WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified.The syntax of the WORKDAY function is as below:=WORKDAY(start_date, days, [holidays])…
  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…
  • Excel EDATE function
    TThe Excel EDATE function returns the serial number that represents the date that is a specified number of months before or after a specified date.The syntax of the EDATE function is as below:=EDATE (start_date, months)…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…

More Excel WEEKNUM Function Examples


  • Converting Week Number to Date
    You need to create a formula based on the MAX function, the DATE function and the WEEKDAY function to convert week number to a date in Excel.…