Date & Time
Sum & Average Daily Revenue by Day of Week (WEEKDAY + SUMIF)
Aggregate daily POS or e-commerce transactions by ISO day of week (Monday = 1 through Sunday = 7) and split weekday vs. weekend totals using WEEKDAY(A2, 2) + SUMIF.
Why WEEKDAY(date, 2) makes Monday–Friday (<= 5) and Saturday–Sunday (>= 6) effortless to aggregate!
Retail staffing and restaurant prep schedules depend on knowing whether Fridays and Saturdays generate 2× Tuesday volume—yet SUMIFS cannot apply WEEKDAY() directly inside its range argument! Adding a clean ISO day-of-week column C2 = WEEKDAY(A2, 2) maps Monday = 1, Tuesday = 2, ..., Friday = 5, Saturday = 6, and Sunday = 7!
Once C2:C8 holds ISO day numbers 1–7, SUMIF(C2:C8, F2, B2:B8) totals any target day of the week (e.g., F2 = 5 sums all Fridays = $4,100), while SUMIF(C2:C8, ">=6", B2:B8) totals Saturday + Sunday weekend sales ($5,350) in a single step!
| Day-of-Week Aggregation Query | ISO WEEKDAY(A2, 2) Code | Exact Formula | Sample Ledger Result |
|---|---|---|---|
| Friday Revenue Total (2 Fridays) | F2 = 5 (Friday) | SUMIF(C2:C8, F2, B2:B8) | $4,100 ($1,950 + $2,150) |
| All Weekend Sales (Sat + Sun) | >= 6 (Sat=6, Sun=7) | SUMIF(C2:C8, ">=6", B2:B8) | $5,350 ($2,900 + $2,450) |
| Monday Revenue Total | F2 = 1 (Monday) | SUMIF(C2:C8, F2, B2:B8) | $1,200 |
Sum Revenue for Any Target ISO Day of Week F2 (1 = Mon ... 7 = Sun)
Totals daily sales in B2:B8 matching target ISO weekday code F2 against C2:C8 = WEEKDAY(A2, 2).
=IF(OR(F2 < 1, F2 > 7), "ISO Day Must Be 1 (Mon) to 7 (Sun)", SUMIF(C2:C8, F2, B2:B8))Sum Weekend Revenue (Saturday = 6 and Sunday = 7)
Totals all weekend dates where WEEKDAY(A2, 2) >= 6.
=SUMIF(C2:C8, ">=6", B2:B8)Try the example
Use the original values or change them to test your understanding.