Sum Only Weekday (or Weekend) Amounts

Excel Formulas › Sum

All versionsSUMPRODUCT

Total only the transactions that fell on weekdays — or just weekends. SUMPRODUCT tests each date’s weekday and sums the matching amounts.

Quick formula: sum amounts that fall on weekdays (Mon–Fri):
=SUMPRODUCT((WEEKDAY(dates, 2) <= 5) * amounts)
WEEKDAY type 2 makes Mon–Fri = 1–5; the test gives 1/0, multiplied by amounts and summed.

Functions used (tap for the full reference guide):

SUMPRODUCTWEEKDAY

The example

Weekday sales separated from weekend.

AB
1DateSales
2Fri200
3Sat150
4Weekday total200

The formula

Test the weekday, sum the matches:

=SUMPRODUCT((WEEKDAY(dates, 2) <= 5) * amounts) // Mon-Fri only

How it works

A weekday test drives a conditional sum:

  1. WEEKDAY(dates, 2) returns 1–7 with Monday = 1, so <= 5 is Mon–Fri.
  2. The comparison yields a 1/0 array; multiply by amounts to zero out the non-matches.
  3. SUMPRODUCT totals the survivors — the weekday sum.
  4. For weekends, use > 5; add a second condition (region, month) by multiplying another array in.

Specific day: (WEEKDAY(dates,2)=1) sums only Mondays. This SUMPRODUCT pattern handles any weekday logic SUMIFS can’t express directly, since SUMIFS has no “weekday” criteria.

Try it: interactive demo

Live demo

Lines “yyyy-mm-dd,amount”.

Weekday · Weekend

Variations

Weekend total

Sat/Sun:

=SUMPRODUCT((WEEKDAY(dates,2)>5)*amounts)

Specific day

Just Mondays:

=SUMPRODUCT((WEEKDAY(dates,2)=1)*amounts)

Weekday + month

Add a factor:

=SUMPRODUCT((WEEKDAY(d,2)<=5)*(MONTH(d)=6)*amt)

Pitfalls & errors

WEEKDAY type. Use type 2 so Mon–Fri = 1–5; the default type makes Sunday = 1 and the test wrong.

Equal-length arrays. Dates and amounts must span the same rows.

Real dates. Text dates won’t feed WEEKDAY.

Practice workbook

📊
Download the free Sum Only Weekday (or Weekend) Amounts practice workbook
A weekday-sum sheet with weekend, specific-day, and month variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I sum only weekday amounts in Excel?
Use =SUMPRODUCT((WEEKDAY(dates, 2) <= 5) * amounts). Type 2 makes Mon-Fri = 1-5, so the test sums weekday rows.
How do I sum weekends?
Use > 5: =SUMPRODUCT((WEEKDAY(dates, 2) > 5) * amounts).
Why can't I use SUMIFS for this?
SUMIFS has no weekday criteria. SUMPRODUCT with WEEKDAY lets you test the day of the week directly.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Count by day of week · SUMPRODUCT formula · Sum by month

Function references: SUMPRODUCT · WEEKDAY