Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count how many of a weekday are in a month in Excel and Google Sheets
Topic:COUNT & counting patternsExcel & Google Sheets
=SUMPRODUCT((WEEKDAY(DATE(YEAR(A2),MONTH(A2),ROW(INDIRECT("1:"&DAY(EOMONTH(A2,0))))),2)=B2)*1)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put any date in the target month in column A (e.g., 2024-03-01).
- 2Put the weekday number to count in column B (1=Mon … 7=Sun using WEEKDAY mode 2).
- 3In C2 enter: =SUMPRODUCT((WEEKDAY(DATE(YEAR(A2),MONTH(A2),ROW(INDIRECT("1:"&DAY(EOMONTH(A2,0))))),2)=B2)*1)
- 4This builds a virtual array of every day in the month and counts how many match the target weekday.
Tips
- A month always has either 4 or 5 of any given weekday — if the result is anything else, check that your weekday number is 1–7.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Can I count Sundays using this formula?
Yes — enter 7 in column B (WEEKDAY mode 2 maps Sunday to 7).
Does EOMONTH handle leap years?
Yes, EOMONTH automatically returns the correct last day, including February 29 in leap years.
More on COUNT & counting patterns
See all →How-to
How to build a frequency distribution
Build a frequency distribution in Excel or Google Sheets using FREQUENCY — count how many values fall in each bin with one array formula, no COUNTIF loop required.
How-toHow to count overlapping date ranges
Count overlapping date ranges in Excel or Google Sheets with SUMPRODUCT — detect how many date intervals share at least one day with a reference range, no VBA required.