This guide will discuss **how to sum by week in Excel using the SUMIFS function**.

##### Table of Contents

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:

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.