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.

x
How to Show All Named Ranges in Excel

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], …)…
Related Posts

How to Sum in Vertical Range
How to Sum in Vertical Range 8

If we want to add numbers based on some conditions in Excel worksheet, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In this article, we will introduce you the method ...

How to Sum in Horizontal Range
How to Sum in Horizontal Range9

To add numbers together we need to apply SUM function. But if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In ...

How to Sum with Criteria and Or Logic in Excel
How to Sum with Criteria and Or Logic in Excel 8

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column
How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column14

Sometimes we may meet the case that subtotal values for different groups and only record a subtotal value for one group in a column, for other cells in this group, keep them blank. Today we will introduce you how can ...

How to Sum by Formula If Cells Are Not Blank in Criteria Range
How to Sum by Formula If Cells Are Not Blank in Criteria13

Sometimes we may meet the cases that some blank cells exist in criteria range or sum range. In most situations we will ignore them, so we need to filter data by ‘not blank/not empty’. Today we will introduce you how ...

How to Sum by Formula if Cell Ends with in Excel
How to Sum by Formula if Cell Ends with in Excel14

We have introduced the method of sum numbers based on criteria “sum if cell begins with” (you can refer to ‘How to Sum Data if Begins with in Excel’ on our website). In this article, we will show you the ...

How to Sum if Less Than A Number in Excel

Sometimes we may meet the case that to sum numbers with the pre-condition “less than X”. We only want to sum numbers which are less than a supplied number. In this article, we will show you the method to resolve ...

How to Sum if Greater Than A Number in Excel
How to Sum if Greater Than A Number in Excel 15

Sometimes we may meet the case that to sum numbers with the pre-condition “greater than X” exists. We only want to sum numbers which are greater than a supplied number. In this article, we will show you the method to ...

How to Sum if Date is Greater Than A Date in Excel

In previous article, we have introduced you the method to sum if date is included in a period, you can check “How to Sum if Date Between Two Dates” on our website for reference. But sometimes we may want to ...

How to Sum if Date Between Two Dates in Excel
How to Sum by Formula if Cell Between Two Dates 18

In previous article, we have introduced you the method to sum if by year, you can check “How to Sum Data if by Year in Excel” on our website for reference. But sometimes we may want to sum numbers not ...

Sidebar