Often you may want to sum the values of some dataset in Excel based on week.

For example, suppose we have the following dataset and we’d like to sum the total sales by week:

The following step-by-step example shows how to do so.

Step 1: Enter the Data

First, enter the data values into Excel:

Step 2: Extract the Week Number from Dates

Next, we need to use the =WEEKNUM() function to extract the week number from each date.

In our example, we’ll type the following formula in cell D2:

=WEEKNUM(A2)

We’ll then drag and fill this formula down to every remaining cell in column D:

Step 3: Find the Unique Weeks

Next, we need to use the =UNIQUE() function to produce a list of unique week numbers.

In our example, we’ll type the following formula in cell F2:

=UNIQUE(D2:D10)

Step 4: Find the Sum by Week

Next, we will use the SUMIF(range, criterion, sum_range) function to find the sum of the sales made during each week.

In our example, we’ll type the following formula in cell G2:

=SUMIF($D$2:$D$10, F2, $B$2:$B$10)

We’ll then drag and fill this formula down to the remaining cells in column G:

This tells us:

• There were 40 total sales made in the first week of the year.
• There were 77 total sales made in the second week of the year.
• There were 38 total sales made in the third week of the year.

And so on.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel: