How to Count Entries by Day of the Week in Excel

Find which weekdays have the most no-shows by counting dates with SUMPRODUCT and WEEKDAY — no helper column.

You will learn to

• Count dates that fall on a given weekday
• Combine a weekday test with a second condition
• Count dates on either of two weekdays

How it works

COUNTIF cannot test the day of the week, but SUMPRODUCT can. WEEKDAY turns each date into a number (1 = Sunday … 7 = Saturday by default), and the comparison WEEKDAY(A2:A13)=2 gives TRUE for Mondays. The double minus (--) turns TRUE/FALSE into 1/0, and SUMPRODUCT adds them: =SUMPRODUCT(--(WEEKDAY(A2:A13)=2)).

Multiply by another condition to count, say, Friday no-shows only. To count either of two days, add the two tests — useful for spotting patterns in appointments, deliveries or support calls.

=SUMPRODUCT(--(WEEKDAY(A2:A13)=2))