Explanation
You might wonder why we aren’t using COUNTIF or COUNTIFS . These functions seem like the obvious solution. However, without adding a helper column that contains a weekday value, there is no way to create criteria for COUNTIF to count weekdays in a range of dates. Instead, we use the versatile SUMPRODUCT function , which handles arrays gracefully without the need to use Control + Shift + Enter. We are using SUMPRODUCT with just one argument, which consists of this expression:
--(WEEKDAY(dates,2)=E5)
Working from the inside out, the WEEKDAY function is configured with the optional argument 2, which causes it to return numbers 1-7 for the days Monday-Sunday, respectively. This makes it easier to list the days in order with the numbers in column E in sequence. WEEKDAY evaluates each date in dates (B5:B15) and returns a number. Because we are giving WEEKDAY 11 dates, we get back an array that contains 11 results like this:
{6;1;1;6;3;7;5;6;5;3;2}
Each number in this array corresponds to a day of the week, with Mondays equal to 1 and Sundays equal to 7. Next, the numbers returned by WEEKDAY are compared to the value in E5, which is 1:
{6;1;1;6;3;7;5;6;5;3;2}=1
The result is an array of TRUE/FALSE values like this:
{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
In this array, TRUE corresponds to dates that fall on Monday and FALSE represents other days of the week. SUMPRODUCT only works with numbers (not text or Booleans) so we use a double-negative (–) to coerce the TRUE/FALSE values to 1s and 0s. The result is delivered directly to the SUMPRODUCT function:
=SUMPRODUCT({0;1;1;0;0;0;0;0;0;0;0}) // returns 2
With just one array to process, SUMPRODUCT sums the items and returns 2 as a final result, since there are two Mondays in the dates.
Dealing with blank dates
If you have blank cells in the list of dates, you will get incorrect results, since the WEEKDAY function will return a result even when there is no date. To handle empty cells, you can adjust the formula as follows:
=SUMPRODUCT((WEEKDAY(dates,2)=E5)*(dates<>""))
Multiplying by the expression (dates<>"") is one way to “cancel out” empty cells.
Without day numbers
The day numbers in column E make the formula easier to understand and write. However, since the day names are already in the range D5:D11, which is named days , it is possible to write a formula that doesn’t use column E at all by using the MATCH function like this:
=SUMPRODUCT(--(WEEKDAY(dates,2)=MATCH(D5,days,0)))
This works because the MATCH function simply returns the position of each day name in days :
MATCH(D5,days,0) // returns 1
MATCH(D6,days,0) // returns 2
MATCH(D7,days,0) // returns 3
Otherwise, the formula works the same way and returns the same counts as the original formula.
Explanation
The YEAR function extracts the year from a valid Excel date . For example:
=YEAR("15-Jun-2021") // returns 2021
In this case, we are giving YEAR and array of dates in the named range dates :
YEAR(dates)
Because dates contains 11 cells, we get back 11 results in an array like this:
{2018;2017;2019;2019;2017;2019;2017;2019;2019;2018;2018}
Each date is compared to the year in column D, which creates a new array of TRUE FALSE values:
{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}
In this array, TRUE corresponds to dates in the year 2017, and FALSE corresponds to dates in different years. Next, we use a double negative (–) to coerce the TRUE FALSE values to 1’s and 0’s. Inside SUMPRODUCT, we now have:
=SUMPRODUCT({0;1;0;0;1;0;1;0;0;0;0})
Finally, with only one array to work with, SUMPRODUCT sums the items in the array and returns a result, 3.
Note: The SUMPRODUCT formula above is an example of using Boolean logic in an array operation . This is a powerful and flexible approach to solving many problems in Excel. It is also an important skill with new functions like FILTER and XLOOKUP , which often use this technique to apply multiple criteria ( FILTER example , XLOOKUP example )
