# How to sum by day of the week in Excel

Turn each date into its day name and compare: `=SUMPRODUCT((TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13)`.

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Date | Sales |  | Day | Total |
| 2026-03-02 | 1840 |  | Mon |  |
| 2026-03-03 | 1625 |  | Tue |  |
| 2026-03-04 | 1710 |  | Wed |  |
| 2026-03-05 | 1930 |  | Thu |  |
| 2026-03-06 | 2480 |  | Fri |  |
| 2026-03-07 | 3150 |  | Sat |  |
| 2026-03-08 | 2045 |  | Sun |  |
| 2026-03-09 | 1765 |  |  |  |
| 2026-03-10 | 1590 |  |  |  |
| 2026-03-13 | 2615 |  |  |  |
| 2026-03-14 | 3320 |  |  |  |
| 2026-03-15 | 2280 |  |  |  |

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

Result: $3,605; $3,215; $1,710; $1,930; $5,095; $6,470; $4,325

## How it works

`TEXT(A2:A13,"ddd")` turns every date into a short day name, like "Mon". Comparing with `D2` gives TRUE on matching days, and multiplying by the sales keeps only those amounts for SUMPRODUCT to add. SUMIFS can't do this because it can't transform the dates first.

## Other ways

- `=SUMPRODUCT((WEEKDAY($A$2:$A$13,2)=MATCH(D2,{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0))*$B$2:$B$13)`: Compares day numbers (Monday = 1 with return type 2), which doesn't depend on the language of day names.

## Common mistakes

- TEXT returns day names in the language of your settings. On a German system "ddd" gives "Mo", so a list typed in English matches nothing. The WEEKDAY version avoids that.
- WEEKDAY without a second argument counts Sunday as 1. Use 2 so Monday is 1 and the weekend is 6 and 7.

## Questions

**How do I sum weekend sales only?**

Use `=SUMPRODUCT((WEEKDAY(A2:A13,2)>5)*B2:B13)`.

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

Divide by the number of matching days: `=SUMPRODUCT((TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13)/SUMPRODUCT(--(TEXT($A$2:$A$13,"ddd")=D2))`.

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