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

Compare the day name of every date with SUMPRODUCT: `=SUMPRODUCT(--(TEXT($B$2:$B$11,"ddd")=D2))`.

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Order | Date |  | Day | Orders |
| W-01 | 2026-03-02 |  | Mon |  |
| W-02 | 2026-03-03 |  | Tue |  |
| W-03 | 2026-03-06 |  | Wed |  |
| W-04 | 2026-03-07 |  | Thu |  |
| W-05 | 2026-03-09 |  | Fri |  |
| W-06 | 2026-03-13 |  | Sat |  |
| W-07 | 2026-03-14 |  | Sun |  |
| W-08 | 2026-03-16 |  |  |  |
| W-09 | 2026-03-20 |  |  |  |
| W-10 | 2026-03-21 |  |  |  |

Formula in E2, filled to E8: `=SUMPRODUCT(--(TEXT($B$2:$B$11,"ddd")=D2))`

Result: 3; 1; 0; 0; 3; 3; 0

## How it works

`TEXT(…,"ddd")` turns every order date into a three-letter day name at once. Comparing with D2 gives TRUE or FALSE for each date, the double minus turns those into 1s and 0s, and SUMPRODUCT adds them up.

## Other ways

- `=SUM(--(TEXT($B$2:$B$11,"ddd")=D2))`: Excel 365 evaluates the array without SUMPRODUCT.

## Common mistakes

- COUNTIF can't test the weekday of a date, because its criteria apply to the cell values themselves, not to a calculation on them. That is why SUMPRODUCT is needed.
- Day names depend on the spreadsheet's language. For a language-independent count, compare WEEKDAY numbers: `WEEKDAY($B$2:$B$11,2)=1` for Monday.

## Questions

**How do I sum sales by weekday?**

Multiply by the amounts: `=SUMPRODUCT((TEXT($B$2:$B$11,"ddd")=D2)*$C$2:$C$11)`.

**How do I find the busiest day?**

`=INDEX(D2:D8,MATCH(MAX(E2:E8),E2:E8,0))`.

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-orders-by-day-of-the-week