How to sum by day of the week in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13)Turn each date into its day name and compare: =SUMPRODUCT((TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13).
- Result on the example
- one result per row in
E2, filled down toE8 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2, filled down to E8. Change any input and the result updates, or edit the formula to experiment.
How it works
TEXT(A2:A13,"ddd") turns every date into a short day name, like "Mon". Comparing with D2 gives TRUE on matching days, and multiplying by the sales keeps only those amounts for SUMPRODUCT to add. SUMIFS can't do this because it can't transform the dates first.
=SUMPRODUCT((TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13)Result3605
$A$2:$A$1312 rows × 1 col: Mar 2, 2026, Mar 3, 2026, Mar 4, 2026, Mar 5, 2026, …A range: 12 cells in one column.
TEXT($A$2:$A$13,"ddd"){12 rows × 1 col} "Mon", "Tue", "Wed", "Thu", …TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".- value
$A$2:$A$1312 rows × 1 col: Mar 2, 2026, Mar 3, 2026, Mar 4, 2026, Mar 5, 2026, … - format_text
"ddd"
- value
D2"Mon"The value in D2.
TEXT($A$2:$A$13,"ddd")=D2{12 rows × 1 col} TRUE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
$B$2:$B$1312 rows × 1 col: $1,840, $1,625, $1,710, $1,930, …A range: 12 cells in one column.
TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13{12 rows × 1 col} 1840, 0, 0, 0, …The * operator multiplies.
SUMPRODUCT((TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13)3605SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13{12 rows × 1 col} 1840, 0, 0, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((WEEKDAY($A$2:$A$13,2)=MATCH(D2,{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0))*$B$2:$B$13) | $3,605, $3,215, $1,710… | Compares day numbers (Monday = 1 with return type 2), which doesn't depend on the language of day names. |
Common mistakes
- TEXT returns day names in the language of your settings. On a German system "ddd" gives "Mo", so a list typed in English matches nothing. The WEEKDAY version avoids that.
- WEEKDAY without a second argument counts Sunday as 1. Use 2 so Monday is 1 and the weekend is 6 and 7.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum by day of the week
E2, total the sales that fell on the weekday in D2, then fill down to E8.Questions
How do I sum weekend sales only?
Use =SUMPRODUCT((WEEKDAY(A2:A13,2)>5)*B2:B13).
How do I average sales by weekday?
Divide by the number of matching days: =SUMPRODUCT((TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13)/SUMPRODUCT(--(TEXT($A$2:$A$13,"ddd")=D2)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.