# How to count dates by day of the week in Excel

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.

**Excel:** `=SUMPRODUCT(--(TEXT($B$2:$B$15,"ddd")=E2))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Delivery | Date |  |  | Day | Deliveries |
| DL-01 | 2026-03-02 |  |  | Mon |  |
| DL-02 | 2026-03-03 |  |  | Tue |  |
| DL-03 | 2026-03-06 |  |  | Wed |  |
| DL-04 | 2026-03-09 |  |  | Thu |  |
| DL-05 | 2026-03-10 |  |  | Fri |  |
| DL-06 | 2026-03-13 |  |  | Sat |  |
| DL-07 | 2026-03-14 |  |  | Sun |  |
| DL-08 | 2026-03-16 |  |  |  |  |
| DL-09 | 2026-03-18 |  |  |  |  |
| DL-10 | 2026-03-20 |  |  |  |  |
| DL-11 | 2026-03-23 |  |  |  |  |
| DL-12 | 2026-03-26 |  |  |  |  |
| DL-13 | 2026-03-27 |  |  |  |  |
| DL-14 | 2026-03-29 |  |  |  |  |

Formula in F2, filled to F8: `=SUMPRODUCT(--(TEXT($B$2:$B$15,"ddd")=E2))`

Result: 4; 2; 1; 1; 4; 1; 1

## 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.

## Other ways

- `=SUMPRODUCT(--(WEEKDAY($B$2:$B$15,2)=MATCH(E2,{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0)))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-dates-by-day-of-week