How to Sum by Day of the Week in Excel

To sum by day of the week in Excel, such as total sales on Mondays or on weekends, combine WEEKDAY with SUMPRODUCT in one formula, or add a helper column of day names and use SUMIFS. Both give the same totals. This guide shows each method on a small sales list, then adds weekend totals, averages per weekday and the mistakes that make the numbers look wrong.

The sample data#

All the examples use eleven days of sales in A1:B12. The dates are real dates, not text:

The day names are shown with a custom date format so you can check the answers. Excel doesn't need them.

Why SUMIFS alone can't sum by weekday#

SUMIFS compares cells with a condition, but it can't run a function on each cell first. Something like =SUMIFS(B2:B12,WEEKDAY(A2:A12,2),1) doesn't work: Excel rejects the formula, because the criteria range must be a real range of cells. You have two ways around that:

1. SUMPRODUCT, which happily works with calculated lists (arrays).
2. A helper column that holds the weekday for each row, so SUMIFS has a real range to check.

Method 1: Sum by day of week with SUMPRODUCT and WEEKDAY#

SUMPRODUCT can test every date and add the matching sales in one cell. For Mondays:

=SUMPRODUCT((WEEKDAY(A2:A12,2)=1)*B2:B12)

The result is 1,265 (420 + 455 + 390). Here is how it works:

• WEEKDAY(A2:A12,2) turns each date into a day number. Return type 2 means Monday = 1 through Sunday = 7.
• =1 gives TRUE for Mondays and FALSE for every other day.
• Multiplying by B2:B12 turns TRUE into 1 and FALSE into 0, so only Monday's sales survive.
• SUMPRODUCT adds the results.

To build a summary for every day, type the numbers 1 to 7 in D2:D8, with day names next to them in E2:E8, and in F2 fill down:

=SUMPRODUCT((WEEKDAY($A$2:$A$12,2)=D2)*$B$2:$B$12)

The seven totals add up to 4,845, the same as =SUM(B2:B12), which is a quick check that no row was missed. For more on how this function multiplies and adds, see the SUMPRODUCT guide and the SUMPRODUCT function reference. The WEEKDAY function guide explains the other return types.

Method 2: a helper column and SUMIFS#

A helper column is easier to read and check, and it also works in a PivotTable. In C2, fill down either of these:

=WEEKDAY(A2,2)
=TEXT(A2,"dddd")

The first gives a number (1 for Monday). The second gives the full day name, such as Monday. Then total with SUMIFS:

=SUMIFS(B2:B12,C2:C12,"Monday")

The result is again 1,265. SUMIFS also lets you add more conditions, such as a store or product column. The SUMIFS function guide shows how.

One warning: TEXT returns day names in the language of your Excel settings, so "Monday" won't match on a computer set to French or German. If the file is shared across languages, use the WEEKDAY number instead.

How to sum weekends or weekdays#

Weekends are days 6 and 7 with return type 2, so one comparison covers both:

=SUMPRODUCT((WEEKDAY(A2:A12,2)>5)*B2:B12)

The result is 1,570 (Saturdays 610 and 700, plus Sunday 260). For Monday to Friday, use <6 instead, which gives 3,275. Together they add up to 4,845.

To limit the total to a date range too, multiply in another condition. With a start date in H1 and an end date in H2:

=SUMPRODUCT((WEEKDAY(A2:A12,2)=1)*(A2:A12>=H1)*(A2:A12<=H2)*B2:B12)

Each extra condition in brackets is another 1 or 0 for each row. For rolling periods, see how to sum the last 30 days.

How to average by day of the week#

A total can mislead when some weekdays appear more often than others. Here there are three Mondays but only one Sunday. Divide by the number of matching days to compare fairly:

=SUMPRODUCT((WEEKDAY(A2:A12,2)=1)*B2:B12)/SUMPRODUCT(--(WEEKDAY(A2:A12,2)=1))

The result is about 421.67: 1,265 over three Mondays. The -- turns TRUE and FALSE into 1 and 0 so SUMPRODUCT can count them. With a day-name helper column, =AVERAGEIFS(B2:B12,C2:C12,"Monday") gives the same answer. Saturday averages 655 a day, the busiest by both measures.

For other time groupings, see how to sum by week and how to sum by month.

Why your weekday totals are wrong#

Most problems come from the dates themselves.

1. Dates stored as text. WEEKDAY can't read text that isn't recognised as a date, so you get #VALUE! or a wrong total. Real dates sit on the right of the cell by default. Convert text dates with DATEVALUE or Text to Columns.
2. The wrong return type. With no second argument, WEEKDAY uses type 1, where Sunday is 1 and Monday is 2. Always write the 2 if you think of Monday as day 1.
3. Ranges of different sizes. A2:A12 with B2:B11 makes SUMPRODUCT return #VALUE!. Make every range cover the same rows.
4. Blank rows inside the range. A blank date counts as day 0, which WEEKDAY treats as a Saturday. Blank sales add 0, so totals are safe, but day counts for averages include them. Keep ranges to the filled rows.
5. Dates with times. WEEKDAY ignores the time part, so 9 Nov 2026 18:30 is still a Monday. No fix needed.

Practise it#

Do the exercise on this page to total Monday sales with one formula. Then practise WEEKDAY, EDATE and EOMONTH in a live spreadsheet and build multi-condition totals in the COUNTIFS and SUMIFS lesson. Each step checks your formula as you type it.