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:

Syntaxt and Arguments

Week_day number

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:

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.