How to count orders by day of the week in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(TEXT($B$2:$B$11,"ddd")=D2))Compare the day name of every date with SUMPRODUCT: =SUMPRODUCT(--(TEXT($B$2:$B$11,"ddd")=D2)).
- Result on the example
- one result per row in
E2, filled down toE8 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2, filled down to E8. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT(--(TEXT($B$2:$B$11,"ddd")=D2))Result3
$B$2:$B$1110 rows × 1 col: 3/2/2026, 3/3/2026, 3/6/2026, 3/7/2026, …A range: 10 cells in one column.
TEXT($B$2:$B$11,"ddd"){10 rows × 1 col} "Mon", "Tue", "Fri", "Sat", …TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".- value
$B$2:$B$1110 rows × 1 col: 3/2/2026, 3/3/2026, 3/6/2026, 3/7/2026, … - format_text
"ddd"
- value
D2"Mon"The value in D2.
TEXT($B$2:$B$11,"ddd")=D2{10 rows × 1 col} TRUE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
-(TEXT($B$2:$B$11,"ddd")=D2{10 rows × 1 col} -1, 0, 0, 0, …Makes the value negative.
--(TEXT($B$2:$B$11,"ddd")=D2{10 rows × 1 col} 1, 0, 0, 0, …Makes the value negative.
SUMPRODUCT(--(TEXT($B$2:$B$11,"ddd")=D2))3SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(TEXT($B$2:$B$11,"ddd")=D2{10 rows × 1 col} 1, 0, 0, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(TEXT($B$2:$B$11,"ddd")=D2)). 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 |
|---|---|---|
| =SUM(--(TEXT($B$2:$B$11,"ddd")=D2))Excel only | 3, 1, 0… | 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)=1for Monday.
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 orders by day of the week
E2, count the orders placed on the day of the week in D2, and fill down to E8.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)).
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.