How to sum values by day of the week in Excel and Google Sheets

Excel & 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

Formula

Runs server-side · free · no signup

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.

Build a summary table with days in one column (Monday through Sunday as numbers 1–7) and a SUMPRODUCT formula referencing that cell so you can see all seven totals at a glance.

Need it for your exact data?

Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.

Use =SUMPRODUCT(($B$2:$B$100)*(WEEKDAY($A$2:$A$100,2)<=5)) which includes all days where the weekday number is 5 or less (Monday through Friday).

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: May 28, 2026