Excel Formula: Count Day of Week in A Date Range
If there is a list of dates, how can you count a certain day of week in such a list of dates as above screenshot shown?
If you want to follow along with this tutorial, please download the example spreadsheet.
Generic formula:
| SUMPRODUCT(--(WEEKDAY(date_range)=week_day)) |
Syntaxt and Arguments
| Date_range: the references of dates that you want to count number of a specific weekday within. |
| Week_day: a number that indicates the day of week. |
Week_day number
| Weekday | Number |
| Sunday | 1 |
| Monday | 2 |
| Tuesday | 3 |
| Wednesday | 4 |
| Thursday | 5 |
| Friday | 6 |
| Saturday | 7 |
Return Value
The formula returns to a numeric value.
How this formula works
For instance, the range B3:B10 list dates, and the corresponding weekday numbers list in the range F3:F9, now you need to count each day of week in this date range, please use below formula:
| =SUMPRODUCT(--(WEEKDAY($B$3:$B$10)=F3)) |
Press Enter key to get the first counting result.
Drag auto fill handle over cells to apply this formula as you need.
Explanation
WEEKDAY function: counts the weekday in a date range. The formula WEEKDAY($B$3:$B$10) returns an array result:
{6;6;3;1;3;7;1;6}
The formula =(WEEKDAY($B$3:$B$10)=F3) returns an array result:
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
SUMPRODUCT function: returns the result of multiplied and summed arrays.
=SUMPRODUCT(--(WEEKDAY($B$3:$B$10)=F3))
=SUMPRODUCT(--{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})
=0
In the formula
= SUMPRODUCT(--(WEEKDAY($B$3:$B$10)=F4))
= SUMPRODUCT(--{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE}
=2
Relative Formulas
- Count Weekdays only Between Two Dates
This tutorial provides formulas to count the days excluding weekends and holidays between two dates - Count Specific Weekday Between Two Dates
This tutorial provides the formula to count a specific weekday between two given dates in Excel. - Count Days Between Two Dates
Here provide formulas to count days, weeks, months or years between two dates. - Convert Time To Decimal Minutes
In this tutorial, it provides a formula for converting time to decimal minutes in Excel.
Relative Functions
- Excel WEEKNUM Function
Returns the week number of the given date in a year - Excel WORKDAY Function
Adds workdays to the given start date and returns a workday - Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date. - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel YEAR Function
The YEAR function returns the year based on the given date in a 4-digit serial number format.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
