How to Sum by Week in Excel

To sum by week in Excel, list the first day of each week and use SUMIFS with two date conditions: on or after the week start, and before the next week starts. One formula, filled down, gives you a weekly total for sales, hours or expenses. This guide shows that method, how to find the Monday of any date, the week-number approach and a pivot table option.

The sample data#

All the examples use this list of daily sales in A1:B8. The dates cover three weeks that start on Monday.

Column A must hold real dates, not text. A real date sits on the right of the cell by default. If yours sit on the left, convert them first, or no formula will add them.

Step 1: List the start of each week#

Type the Monday of each week in a column, for example D2:D4: 2 Mar 2026, 9 Mar 2026 and 16 Mar 2026. You can type the first one and use =D2+7 in D3, filled down, so you never miscount.

If you'd rather work it out from the data, this returns the Monday of the week for the date in A2:

=A2-WEEKDAY(A2,2)+1

WEEKDAY with return type 2 numbers the days Monday = 1 to Sunday = 7. Subtract that number and add 1, and you land on Monday. For Sunday 15 March, WEEKDAY returns 7, so the formula gives 15 − 7 + 1 = 9 March. Format the result as a date with Ctrl+1 (Mac: ⌘1).

For weeks that start on Sunday, use =A2-WEEKDAY(A2)+1. With no return type, WEEKDAY numbers Sunday = 1 to Saturday = 7.

Step 2: Sum by week with SUMIFS#

SUMIFS adds the numbers that meet every condition you give it. For one week, you need two conditions on the same date column. In E2:

=SUMIFS($B$2:$B$8,$A$2:$A$8,">="&D2,$A$2:$A$8,"<"&D2+7)

Here is what each part does:

• $B$2:$B$8 is the range to add, the amounts.
• ">="&D2 keeps dates on or after the week start. The & joins the operator to the date in D2.
• "<"&D2+7 keeps dates before the next Monday.

Fill down to E4 (Windows: Ctrl+D; Mac: ⌘D). The $ signs keep the data ranges fixed while D2 moves to D3 and D4.

The first week is 120 + 85 + 60 = 265. Sunday 15 March falls in the week of 9 March, so the second week is 150 + 40 + 95 = 285.

The SUMIFS function guide explains the argument order. The same two-condition pattern powers a sum between two dates and a sum by month.

Why use "<" next week instead of "<=" the last day#

Ending the range at less than the next week's start is safer than less than or equal to the last day. Both give the same result for plain dates. But if your dates include times, such as 15 Mar 2026 14:30, the value is slightly more than 15 March. A condition of "<="&D2+6 would leave it out; "<"&D2+7 includes it.

The same habit works for months and years, which is why the guides to a month-to-date total and a year-to-date total use it too.

Sum by week number instead#

A week number is a count of weeks from the start of the year. Some reports label weeks this way ("Week 10"). Add a helper column C with:

=ISOWEEKNUM(A2)

ISOWEEKNUM follows the ISO standard: weeks start on Monday, and week 1 is the week that contains the first Thursday of the year. Mon 2 Mar 2026 is in week 10. Then total a week with SUMIF:

=SUMIF($C$2:$C$8,10,$B$2:$B$8)

That returns 265. A few warnings:

• WEEKNUM is the older function. =WEEKNUM(A2,2) starts weeks on Monday but counts the week containing 1 January as week 1. It can disagree with ISOWEEKNUM, especially in early January and late December.
• Week numbers repeat every year. If your data covers more than one year, add the year to the helper, such as =YEAR(A2)&"-W"&ISOWEEKNUM(A2), and sum on that. Note that the ISO year can differ from the calendar year for dates around New Year.
• The week-start method avoids both problems, which is why it is the better default.

Sum by week with a pivot table#

A pivot table can group dates into weeks without any formula:

1. Select the data and go to Insert > PivotTable, then click OK.
2. Drag Date to Rows and Amount to Values.
3. Right-click any date in the pivot and choose Group.
4. Select Days only (deselect Months and any others), and set Number of days to 7.
5. Set Starting at to a Monday, such as 2 March 2026 typed in your usual date format, and click OK.

The pivot shows one row per week, labelled with its first and last date (2 March to 8 March for the first week), with the same totals: 265, 285 and 70. If Starting at isn't a Monday, the weeks will start on whatever day it is, so check it.

Why your weekly totals are wrong or zero#

When a weekly SUMIFS returns 0 or a strange number, check these:

1. Dates stored as text. SUMIFS can't compare text with a date. Convert the column with DATEVALUE or Data > Text to Columns.
2. The operator outside the quotes. Write ">="&D2, not >=D2 or ">=D2". Inside quotes, D2 is just two letters.
3. The wrong week start. If D2 is a Wednesday, each "week" runs Wednesday to Tuesday. Use the WEEKDAY formula to get Mondays.
4. Ranges not locked. Without $, the ranges slide down as you fill, and later weeks miss rows.
5. Ranges of different sizes. SUMIFS returns #VALUE! if the sum range and date range have different numbers of rows.

For yearly totals with the same approach, see how to sum by year.

Practise it#

Try the exercise on this page: one SUMIFS with two date conditions gives the first week's total. Then practise dates in criteria in a live spreadsheet, where you total by date range and month, and practise COUNTIFS and SUMIFS to get fast at multi-condition totals. Every formula is checked instantly.