This guide will discuss how to sum by week in Excel using the SUMIFS function.
Excel is an excellent tool to use for different statistical and mathematical calculations. Since it contains several built-in functions and tools, it makes it really easy and simple to perform calculations in different situations.
For instance, we can easily calculate the sum by week in Excel using a built-in function. So we will be using the SUMIFS function to calculate the sum by week in Excel. However, we can also explore another method using the SUMPRODUCT function together with the WEEKNUM function.
Let’s take a sample scenario wherein we must get the sum by week in Excel.
Suppose you have a sales report. And you need to obtain the total sum of sales by week based on the report. To make your work easier, you opted to perform the calculations in Excel using the SUMIFS function.
Before we dive into a real example of how to sum by week in Excel, let’s first explore how to write the different functions we will utilize.
The Anatomy of the SUMIFS Function
The syntax or the way we write the SUMIFS function is as follows:
=SUMIFS(sum_range, criteria_range1, criteria1,...)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we begin any function in Excel.
- SUMIFS() refers to our
SUMIFSfunction. And this function is used to add the cells we specify based on the given set of conditions or criteria we input. - sum_range is a required argument. So it refers to the actual cells to sum.
- criteria_range1 is another required argument. And it is the range of cells we want to be evaluated for the given condition.
- criteria1 is also a required argument. So it refers to the criteria or conditions we set as a number, expression, or text that defines which of the cells will be added.
The Anatomy of the WEEKNUM Function
The syntax or the way we write the WEEKNUM function is as follows:
=WEEKNUM(serial_number, [return_type)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we activate any function in Excel.
- WEEKNUM() is our
WEEKNUMfunction. And this function is used to return the week number in the year. - serial_number is a required argument. So this refers to the date-time code used by Excel for date and time calculation.
- return_type is an optional argument. And this refers to a number that determines the type of return value. So this can either be 1 or 2.
Great! Now let’s move on to a real example of how to sum by week in Excel.
A Real Example of How to Sum by Week in Excel
Let’s say we have a data set containing the total sales of different dates. And we want to calculate the sum of the total sales by week. So our initial data set would look like this:
Firstly, we can use the SUMIFS function to get the sum by week in Excel. However, we would need to create a helper column first using the WEEKNUM function. So the WEEKNUM function will give us the specific number of weeks of each sales date.
After getting the week number, we can proceed and use the SUMIFS function to get the sum of all the sales that occur in a specific week number. For instance, we will add all the sales that happened in week 1.
Secondly, we can also get the sum by week in Excel without using a week number helper column. In this case, we will utilize the SUMPRODUCT function and the WEEKNUM function to get the sum by week in Excel.
So the SUMPRODUCT function will list the array of values from the cell ranges. Next, we will use the WEEKNUM function to calculate the week number for each sales date. Additionally, the WEEKNUM function does not support array values. So we need to add zero in the formula for it to function properly.
Then, the WEEKNUM function will change the 1 values to TRUE values. Afterward, we will input double dashes to convert the TRUE and FALSE values into 1s and 0s.
Finally, the SUMPRODUCT function will multiply each pair in the arrays to return an array of several sales that have a common week number. And we can repeat this formula for the other weeks.
So our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now let’s discuss the steps of how to sum by week in Excel.
How to Sum by Week in Excel
In this section, we will explain the step-by-step process of how to sum by week in Excel. Furthermore, we will discuss two methods to perform this task.
1. Firstly, let’s try using the SUMIFS function together with the WEEKNUM function. So we need to first create a helper column using the WEEKNUM function. To do this, we will create a new column wherein we will input the week number of each sales date.
Then, we will type in the formula “=WEEKNUM(B3,1)”. Lastly, press the Enter key to return the result.
2. Secondly, we will drag down the Fill Handle tool to copy the formula.
3. Thirdly, we will create another table to place the different week numbers and the sum of each week. So we will input the different week numbers based on the results. Next, we will input the formula “=SUMIFS(C3:C7,D3:D7, F3)”.
Lastly, we will press the Enter key to return the results. And we will do the same for the other weeks.
4. And tada! We have successfully calculated the sum by week in Excel.
5. Next, let’s try getting the sum by week using the SUMPRODUCT function together with the WEEKNUM function. Similarly, we will create another table to input the results.
In the first column, we will place the different week numbers, In the second column, we will type in the formula “=SUMPRODUCT(–(WEEKNUM(B10:B14+0, 1)=E10), C10:C14)”. Finally, we will press the Enter key to return the results.
6. Next, we will drag down the Fill Handle tool to apply the formula to the other cells.
7. And tada! We have calculated the sum by week.
And that’s pretty much it! We have explained how to sum by week in Excel using two easy and simple methods. Now you can choose any of the two and apply it to your work.
Are you interested in learning more about what Excel can do? You can now use the WEEKNUM function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
