# How to Sum by Week in Excel

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 `SUMIFS` function. 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 `WEEKNUM` function. 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: 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. Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

##### You May Also Like ## How to Apply the Central Limit Theorem in Excel

This guide will explain how to apply the central limit theorem in Excel. You can use the central… ## How to Perform a Normality Test in Excel

This guide will explain how to perform a normality test in Excel. We can use the normality test… ## How to Paste in Reverse Order in Excel

This guide will explain how to paste a range of values in reverse order in Excel. We’ll provide… ## How to Calculate an Exponential Moving Average in Excel

This guide will explain how to calculate an exponential moving average in Excel. Since it has several built-in…  