How to count dates by day of the week in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(TEXT($B$2:$B$15,"ddd")=E2))Compare each date's short day name with the day in your table: =SUMPRODUCT(--(TEXT($B$2:$B$15,"ddd")=E2)), filled down from Mon to Sun.
- Result on the example
- one result per row in
F2, filled down toF8 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F8. Change any input and the result updates, or edit the formula to experiment.
How it works
TEXT(date,"ddd") turns each date into its three-letter day name, "Mon" to "Sun". Comparing the whole column with E2 gives TRUE for the matching days, and SUMPRODUCT counts them. The dates are locked with $ so every row of the summary looks at the same list.
Four deliveries landed on a Monday and four on a Friday.
=SUMPRODUCT(--(TEXT($B$2:$B$15,"ddd")=E2))Result4
$B$2:$B$1514 rows × 1 col: Mar 2, 2026, Mar 3, 2026, Mar 6, 2026, Mar 9, 2026, …A range: 14 cells in one column.
TEXT($B$2:$B$15,"ddd"){14 rows × 1 col} "Mon", "Tue", "Fri", "Mon", …TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".- value
$B$2:$B$1514 rows × 1 col: Mar 2, 2026, Mar 3, 2026, Mar 6, 2026, Mar 9, 2026, … - format_text
"ddd"
- value
E2"Mon"The value in E2.
TEXT($B$2:$B$15,"ddd")=E2{14 rows × 1 col} TRUE, FALSE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
-(TEXT($B$2:$B$15,"ddd")=E2{14 rows × 1 col} -1, 0, 0, -1, …Makes the value negative.
--(TEXT($B$2:$B$15,"ddd")=E2{14 rows × 1 col} 1, 0, 0, 1, …Makes the value negative.
SUMPRODUCT(--(TEXT($B$2:$B$15,"ddd")=E2))4SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(TEXT($B$2:$B$15,"ddd")=E2{14 rows × 1 col} 1, 0, 0, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(TEXT($B$2:$B$15,"ddd")=E2)). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(WEEKDAY($B$2:$B$15,2)=MATCH(E2,{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0))) | 4, 2, 1… | Uses day numbers instead of names, so it doesn't depend on the language Excel uses for day names. |
Common mistakes
- Day names from TEXT follow the language of your Excel. In a German or French Excel, "ddd" gives "Mo" or "lun.", so a table typed in English never matches. Day numbers from WEEKDAY avoid this.
- Using "dddd" gives full names ("Monday"). The format code and the labels in column E have to match.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count dates by day of the week
F2, count the deliveries that fall on the day of the week in E2, then fill down to F8.Questions
How do I count only one weekday, like Mondays?
Put the day in a cell, or use the number: =SUMPRODUCT(--(WEEKDAY(B2:B15,2)=1)) counts Mondays.
How do I add a day-of-week column instead?
Use =TEXT(B2,"dddd") next to each date, then a pivot table or COUNTIF on that column.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.