Count a Weekday in a Month

All versionsSUMPRODUCT

How many Mondays are in this month? SUMPRODUCT over the month’s days, testing the weekday, counts them — useful for staffing, payroll periods, and scheduling.

Quick formula: count Mondays in the month of A1 (weekday 2):

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&EOMONTH(A1,0)))) = 2) * 1)

It walks every day of the month and counts those whose weekday matches. A simpler version uses the day numbers directly.

Functions used (tap for the full reference guide):

SUMPRODUCT WEEKDAY

The example

June 2026 has 5 Mondays.

	A	B
1	Month	# Mondays
2	June 2026	5

The formula

Count matching weekdays across the month:

=SUMPRODUCT((WEEKDAY(firstDay - 1 + ROW(INDIRECT("1:"&day(EOMONTH(firstDay,0)))), 2) = 1)*1) // # of Mondays

How it works

Build the month’s days, test the weekday, sum the hits:

Generate the day-of-month sequence with ROW(INDIRECT("1:"&daysInMonth)).
Add them to (first day − 1) to get every actual date in the month.
WEEKDAY(…, 2)=1 tests for Monday (type 2: Mon=1); multiply by 1 to turn TRUE/FALSE into 1/0.
SUMPRODUCT sums the 1s — the count of that weekday. Change the =1 for other days.

If you already have a date column, it’s far simpler: =SUMPRODUCT((WEEKDAY(dates,2)=1)*(MONTH(dates)=6)) counts Mondays in June from a list. The INDIRECT version is for when you only have the month, not a list of dates.

Try it: interactive demo

Live demo

Pick a month and weekday.

Month

Weekday

Count:

Variations

From a date list

Simpler if you have dates:

=SUMPRODUCT((WEEKDAY(dates,2)=1)*(MONTH(dates)=6))

Weekdays in month

All working days:

=NETWORKDAYS(firstDay, EOMONTH(firstDay,0))

Weekends in month

Total days minus workdays.

Pitfalls & errors

INDIRECT is volatile. The text-built range recalculates often; on big models prefer a real date list or NETWORKDAYS.

WEEKDAY type. Use type 2 (Mon=1) consistently, or your target number won’t match the day you mean.

First day of month. Anchor on DATE(y,m,1) so the day sequence starts correctly.

Practice workbook

📊

Download the free Count a Weekday in a Month practice workbook

A weekday-counter with the date-list and NETWORKDAYS variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count how many Mondays are in a month in Excel?

Use SUMPRODUCT over the month's days testing WEEKDAY(...,2)=1. If you have a date list, it's simpler: =SUMPRODUCT((WEEKDAY(dates,2)=1)*(MONTH(dates)=targetMonth)).

How do I count all working days in a month?

Use =NETWORKDAYS(firstDay, EOMONTH(firstDay,0)) — it counts Mon-Fri across the month.

Why is my count off?

Check the WEEKDAY return type — with type 2, Monday is 1. Mismatched types count the wrong day.

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

Function references: SUMPRODUCT · WEEKDAY