How to sum by week in Excel

Step 1: find the week start for each date

WEEKDAY(A2,2) numbers the days Monday = 1 through Sunday = 7. Subtracting that and adding 1 takes any date back to its Monday: Tuesday 15 Dec goes back one day, Sunday 20 Dec goes back six. Column D in the sample does this for every row.

The result is a real date, so format it as a date (for example ddd d mmm) rather than General, or it shows as a number like 46370. This helper column is also handy on its own: you can filter or sort by it, and it is easy to check by eye.

Step 2: a weekly summary with SUMIFS

Put the Monday of each week in F2:F5. Type the first one, then =F2+7 in F3 and fill down. In G2, SUMIFS adds the sales dated on or after that Monday and before the next Monday. Lock the data ranges with $ so they stay put when you fill down.

This version does not need the helper column at all, and it copes with dates that carry a time, such as 24/12/2026 16:40, because "<" the next Monday includes the whole of Sunday. The four weeks come to 6,075, 7,560, 4,905 and 2,605.

Simpler with the helper column: SUMIF

If column D already holds each row's week start, a single-condition SUMIF is enough: match the week start and add the sales. It is easier to read and explain to someone else, at the cost of one extra column.

Add more conditions with SUMIFS. Shop sales for the week of 21 December are =SUMIFS($C$2:$C$11,$D$2:$D$11,F3,$B$2:$B$11,"Shop"), which gives 4,150.

Weeks that start on Sunday

Retail and US calendars often run Sunday to Saturday. Use WEEKDAY with no second argument (Sunday = 1) and the same subtract-and-add-1 pattern. Your summary list then needs Sundays instead of Mondays.

Choosing the start day changes the totals, not just the labels. The 1,975 on Sunday 20 December sits in the 14 December week with Monday weeks, and in the 20 December week with Sunday weeks. Agree the rule before you compare weekly figures with anyone else's report.

Why not WEEKNUM? The year-end trap

WEEKNUM(date,2) returns a Monday-start week number, and summing by week number looks tidy. But WEEKNUM restarts at 1 every 1 January, so the week of 28 December 2026 is split in two: 28 to 31 December get week 53, and 1 to 3 January get week 1. The 2,305 from 2 January lands in a different "week" from the 980 and 1,620 earlier that week, and week 1 of 2027 also collides with week 1 of every other year.

ISOWEEKNUM follows the ISO 8601 rule (weeks start on Monday, week 1 contains the first Thursday of the year), which keeps the 28 December week together as week 53 of 2026. Even so, a week-start date is easier: it never repeats across years, it sorts correctly, and it tells the reader which week they are looking at. Totals for the 28 December week come to 4,905 whichever year the days fall in.

No formulas: PivotTable grouping by 7 days

A PivotTable can group dates into weeks for you. The key detail is the start date: Excel starts the first group on the earliest date in the data, which is a Tuesday here, so set it to a Monday by hand or every week runs Tuesday to Monday.

1. Select the data and go to Insert > PivotTable, then click OK.
2. Drag Date to Rows and Sales to Values.
3. Right-click any date in the PivotTable and choose Group.
4. In the Grouping box, select Days only (click Months, Quarters and Years to clear them).
5. Set Number of days to 7.
6. Change Starting at to a Monday on or before the first date, such as 12/14/2026, then click OK.