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)
This will produce a list of unique week numbers:
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:
How to Sum by Month in Excel
How to Sum by Category in Excel
How to Sum Across Multiple Sheets in Excel
Why is January 3 considered the second week????
How do I total by week for a payroll when my week runs Monday through Sunday?
To **sum payroll totals by week (Monday through Sunday)** in Excel, you can use a combination of `SUMIFS`, `WEEKNUM`, and `TEXT` functions — but with a twist to **start the week on Monday**, which Excel doesn’t default to.
### 🧾 Step-by-Step: Summing Payroll by Week (Monday–Sunday)
#### ✅ 1. **Your Data Format**
Let’s say your payroll data is like this:
| A (Date) | B (Amount) |
|————–|————|
| 04/01/2024 | 200 |
| 04/02/2024 | 220 |
| … | … |
#### ✅ 2. **Add a “Week Start” Helper Column (Column C)**
In `C2`, enter this formula to get the **Monday of the week** for each date:
“`excel
=A2-WEEKDAY(A2, 2)+1
“`
Then drag it down. This formula:
– Uses `WEEKDAY(date, 2)` to treat **Monday as the first day of the week**.
– Subtracts the weekday index and adds 1 to get that Monday.
| C (Week Start) |
|—————-|
| 04/01/2024 |
| 04/01/2024 |
| 04/08/2024 |
| … |
#### ✅ 3. **Create a Unique List of Week Start Dates**
In a separate column (say column E), copy and paste the `Week Start` values from column C, then:
– Go to **Data > Remove Duplicates**.
– Now you have one row per week.
#### ✅ 4. **Sum Payroll by Week**
In column F next to your week start list, use this formula in `F2`:
“`excel
=SUMIFS(B:B, C:C, E2)
“`
This tells Excel to:
– Sum all values in `B:B` (Amounts),
– Where `C:C` (Week Start) matches the date in `E2`.
| E (Week Start) | F (Weekly Total) |
|—————-|——————|
| 04/01/2024 | 1540 |
| 04/08/2024 | 1625 |
—
I like your solution. However, I have data that covers multiple years. When I used your solution, the week numbers from multiple years were added together. I concatenated the week # and year to create a unique value in another column and then used =sumif to total that column by week. Anyway, you got me off to a good start and I learned the =weeknum function which I was unaware of. Thanks,
Thank you Charles for your contribution to our discussion!