Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sum values by day of the week in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUMPRODUCT(($B$2:$B$5)*(WEEKDAY($A$2:$A$5,2)=1))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Ensure your date column (e.g. A) contains real date values.
- 2Use WEEKDAY with mode 2 (Monday=1, Sunday=7) to number the days: WEEKDAY($A$2:$A$100,2)=1 produces TRUE/FALSE for Mondays.
- 3Multiply by the value column inside SUMPRODUCT: =SUMPRODUCT(($B$2:$B$100)*(WEEKDAY($A$2:$A$100,2)=1)). Change =1 to =2 for Tuesdays, =6 for Saturdays, =7 for Sundays.
Tips
- Use WEEKDAY mode 2 (Monday=1, Sunday=7) for cross-region work. Mode 1 (Sunday=1) is the US-Excel default and silently flips Sat/Sun on European sheets.
- Build a one-shot summary table: list day numbers 1-7 in E2:E8 and write =SUMPRODUCT(($B$2:$B$100)*(WEEKDAY($A$2:$A$100,2)=E2)) so all seven totals update together.
- Wrap with IFERROR for empty input ranges: =IFERROR(SUMPRODUCT(...),0) returns 0 instead of #VALUE! when the data range is filtered empty.
- Use named ranges for the date and value columns so the formula reads SUMPRODUCT((Sales)*(WEEKDAY(Dates,2)=1)) — far easier to audit than absolute references.
- For dynamic data that grows, replace $A$2:$A$100 with $A:$A (whole column) in Sheets; in Excel use a Table reference like Sales[Date] so new rows are included automatically.
- SUMIF cannot do this directly because its criterion runs against the cell value, not a derived weekday. SUMPRODUCT is the canonical pattern; SUMIFS with a helper WEEKDAY column is the alternative when the file already has one.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I sum only weekdays (Monday to Friday) in one formula?
Use =SUMPRODUCT(($B$2:$B$100)*(WEEKDAY($A$2:$A$100,2)<=5)) which includes all days where the WEEKDAY mode-2 result is 5 or less (Monday through Friday).
How do I sum only weekends (Saturday and Sunday)?
Use =SUMPRODUCT(($B$2:$B$100)*(WEEKDAY($A$2:$A$100,2)>5)) — mode-2 returns 6 for Saturday and 7 for Sunday, so >5 captures both.
Can I sum by multiple specific weekdays at once, like Monday and Wednesday?
Yes: =SUMPRODUCT(($B$2:$B$100)*((WEEKDAY($A$2:$A$100,2)=1)+(WEEKDAY($A$2:$A$100,2)=3))) — the + acts as logical OR inside SUMPRODUCT.
How do I exclude holidays from the weekday sum?
Combine with NETWORKDAYS logic: =SUMPRODUCT(($B$2:$B$100)*(WEEKDAY($A$2:$A$100,2)<=5)*(ISNA(MATCH($A$2:$A$100,Holidays,0)))) where Holidays is a named range of holiday dates.
How do I group totals by week (e.g. weekly subtotals)?
Use WEEKNUM instead of WEEKDAY: =SUMPRODUCT(($B$2:$B$100)*(WEEKNUM($A$2:$A$100,2)=E2)) where E2 holds the week number you want.
Why is my result showing as a date or strange number?
The result cell inherited date formatting from the source. Select the cell, press Ctrl+1, and switch to Number or Currency format.
Does this work the same way in Google Sheets?
Yes — SUMPRODUCT and WEEKDAY behave identically in Sheets, with the same mode-1/mode-2 semantics. The formula above pastes in unchanged.
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.