Count Dates by Day of Week

Excel Formulas › Count

All versionsSUMPRODUCTWEEKDAY

To count how many dates fall on a particular weekday — how many Mondays, how many weekend orders — pair WEEKDAY with SUMPRODUCT. WEEKDAY turns each date into a day number; SUMPRODUCT counts the matches.

Quick formula: to count how many dates in A2:A10 fall on a Monday:
=SUMPRODUCT(--(WEEKDAY(A2:A10) = 2))
WEEKDAY returns 1–7 (Sunday=1 by default, so Monday=2); the -- turns TRUE/FALSE into 1/0 for SUMPRODUCT to add.

Functions used (tap for the full reference guide):

WEEKDAYSUMPRODUCT

The example

A handful of dates; count the Mondays.

AB
1DateDay
26/1/2026Mon
36/2/2026Tue
46/8/2026Mon
56/13/2026Sat
6Mondays:2

The formula

The Monday count:

=SUMPRODUCT(--(WEEKDAY(A2:A5) = 2)) // Jun 1 and Jun 8 are Mondays → 2

How it works

Three steps inside one formula:

  1. WEEKDAY(A2:A5) converts each date to a number 1–7. By default Sunday is 1, so Monday is 2.
  2. Comparing = 2 builds a TRUE/FALSE array marking the Mondays.
  3. The double negative -- coerces TRUE/FALSE to 1/0, and SUMPRODUCT adds them up — 2.
  4. Change the 2 to target any day, or test >=6 (with the right WEEKDAY type) to count weekends.

Count weekends in one shot: =SUMPRODUCT(--(WEEKDAY(A2:A10,2)>=6)). The 2 as WEEKDAY’s second argument makes Monday=1…Sunday=7, so Saturday and Sunday are 6 and 7.

Try it: interactive demo

Live demo

Pick a weekday; count how many sample dates land on it.

Count:

Variations

Count weekends

WEEKDAY type 2 makes Sat/Sun = 6/7:

=SUMPRODUCT(--(WEEKDAY(A2:A10, 2) >= 6))

Count a weekday in a date range too

Multiply conditions:

=SUMPRODUCT((WEEKDAY(A2:A10)=2) * (A2:A10>=E1) * (A2:A10<=E2))

Count weekday by name with TEXT

Alternative using the day name:

=SUMPRODUCT(--(TEXT(A2:A10,"ddd")="Mon"))

Pitfalls & errors

WEEKDAY numbering depends on the 2nd argument. The default makes Sunday=1. Pass 2 to make Monday=1. Match your comparison number to the type you chose.

Forgetting the --. Without coercion, SUMPRODUCT may add TRUE/FALSE as text and return 0. The double-negative (or multiplying by 1) fixes it.

COUNTIF can’t do this directly. COUNTIF has no way to evaluate WEEKDAY per cell, so SUMPRODUCT (or a helper column) is the tool.

Practice workbook

📊
Download the free Count Dates by Day of Week practice workbook
Dates with live SUMPRODUCT/WEEKDAY counts, the weekend and date-range variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count how many dates fall on a certain weekday in Excel?
Use =SUMPRODUCT(--(WEEKDAY(range) = n)) where n is the day number (Sunday=1 by default, Monday=2). The -- converts TRUE/FALSE to 1/0 for SUMPRODUCT to sum.
How do I count weekends?
Use WEEKDAY type 2 so Saturday and Sunday are 6 and 7: =SUMPRODUCT(--(WEEKDAY(range, 2) >= 6)).
Can I count a weekday within a date range?
Yes, multiply the conditions: =SUMPRODUCT((WEEKDAY(range)=2)*(range>=start)*(range<=end)).

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 dates between two dates · Sum by month

Function references: WEEKDAY · SUMPRODUCT