Formula to count dates by weekday in Excel

When do we use this formula?

In fact, there are many cases need to use this formula to solve the problem!

Suppose you have a row contains day have the event, and you want to count how many events on Monday!

Example:

A B
Weekday Event
1 Mon Event
2 Mon Event
3 Sat Event
4 Web Event
5 Sat Event
6 Tue Event
7 Tue Event
8 Thu Event

Try to use: =SUMPRODUCT(--(WEEKDAY(A1,A8)=1))

The result is 2, 1 is Monday.

We have general formula: =SUMPRODUCT(--(WEEKDAY(dates)=day_num))

  • dates are range that has to contain weekday.
  • day_num is numbered 1-7 for the days Monday-Sunday