How to sum values by day of the week
✓ Verified in LibreOffice 25.8.7.3Total all the Mondays (or any weekday) in dated data — traffic by weekday, weekend vs weekday sales.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =SUMPRODUCT((WEEKDAY(A2:A100,2)=1)*B2:B100) | Mode 2: Monday=1..Sunday=7. Change the =1 for other days; >5 for weekends. |
| Google Sheets | =SUMPRODUCT((WEEKDAY(A2:A100,2)=1)*B2:B100) | Identical. |
| LibreOffice Calc | =SUMPRODUCT((WEEKDAY(A2:A100,2)=1)*B2:B100) | Identical. |
How it works
WEEKDAY runs over the whole date column (as an array), the =1 test keeps Mondays, and SUMPRODUCT totals the matching amounts: the two Mondays here (serials 46223 = July 20 and 46230 = July 27, 2026) carry 100 + 25 = 125, while Tuesday's 50 is excluded. There's no SUMIF criterion that can see weekdays directly — this computed-condition pattern (or a helper column of WEEKDAY values feeding SUMIF) is the standard route.
Verified, not just documented
We ran =SUMPRODUCT((WEEKDAY(A2:A4,2)=1)*B2:B4) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 125 — exactly the expected result. Every formula here is confirmed by actually executing it.