How to sum by day of the week in Excel

SUMPRODUCT and WEEKDAY, no helper column

You run a coffee cart, a market stall or a small shop, and the question you keep coming back to is "which days are actually worth opening?". The sheet logs two weeks of takings, one row per sale session, with dates in column A and amounts in column B.

WEEKDAY(A2:A15,2) turns each date into a day number, Monday 1 to Sunday 7. Comparing that to 6 gives TRUE for the Saturday rows, and multiplying by B2:B15 keeps only their amounts. SUMIFS can't do this on its own, because its criteria test the cell value, and the cell holds a date, not a day name.

For the full table, the day numbers sit in F2:F8 and G2 holds =SUMPRODUCT((WEEKDAY($A$2:$A$15,2)=F2)*$B$2:$B$15), filled down. The totals come out at Mon 1,186, Tue 587, Wed 310, Thu 699, Fri 1,008, Sat 1,375 and Sun 260, which add up to the 5,425 in column B.

A helper column with SUMIF (and PivotTables)

If you'd rather see the day on every row, add =TEXT(A2,"ddd") in column C and fill it down. Then =SUMIF(C2:C15,"Sat",B2:B15) gives the same 1,375, and in Excel 365 =SUMIF(C2:C15,E2:E8,B2:B15) spills all seven totals next to the Mon-Sun labels in E2:E8.

Don't point SUMIF at the date column with "Sat": =SUMIF(A2:A15,"Sat",B2:B15) returns 0, because no cell contains the text Sat. Also note that TEXT's day names and format codes follow the language Excel runs in, so on a computer set to another language a "Sat" criterion can find nothing. The WEEKDAY number version works in any language.

The helper column is also the only way to get weekdays into a PivotTable. PivotTables group dates by anything from seconds to years, but not by weekday.

1. Add the Day column with =TEXT(A2,"ddd") and fill it down.
2. Click in the data and choose Insert > PivotTable > OK.
3. Drag Day to Rows and Sales to Values.
4. Excel sorts the days Sun to Sat using its built-in custom lists, not A to Z. To start on Monday, drag the Mon row to the top.

Average per weekday: per row, per open day, or per calendar day

This is where most weekday reports go wrong. =AVERAGEIF(C2:C15,"Sat",B2:B15) returns 458.33, but that's the average per row. Saturday 14 November has two rows (a morning market and an evening pop-up), so the average is split three ways when there were only two Saturdays.

Divide the total by the number of Saturdays instead. =NETWORKDAYS.INTL(MIN(A2:A15),MAX(A2:A15),"1111101") counts the Saturdays in the period, 2, so the real figure is 687.50 per Saturday. If you only want days you were open, count distinct dates: =COUNT(UNIQUE(FILTER(A2:A15,WEEKDAY(A2:A15,2)=6))) is also 2 here.

The two divisors differ when you had closed days. The cart was shut on Wednesday 11 November, so Wednesday averages 310 per day it opened but 155 per calendar Wednesday. Sunday is 260 vs 130. Use "per open day" to judge how good a day is, and "per calendar day" to judge what closing costs you.

Busiest day and weekend share

With the totals in G2:G8, =XLOOKUP(MAX(G2:G8),G2:G8,E2:E8) names the best day: Sat. =SORTBY(E2:E8,G2:G8,-1) ranks all seven: Sat, Mon, Fri, Thu, Tue, Wed, Sun. Mondays at number two is the kind of surprise that's worth a second look before you change your hours.

To compare weekends with weekdays, test >5 and <6: =SUMPRODUCT((WEEKDAY(A2:A15,2)>5)*B2:B15) returns 1,635 and the <6 version returns 3,790. Divide the weekend figure by the total for its share.

Add a date range or another condition

Each extra condition is one more bracket multiplied in. Saturdays from 9 November onward: =SUMPRODUCT((WEEKDAY(A2:A15,2)=6)*(A2:A15>=DATE(2026,11,9))*B2:B15) returns 735. With the helper column, SUMIFS reads more easily: =SUMIFS(B2:B15,C2:C15,"Sat",A2:A15,">="&DATE(2026,11,9)) gives the same 735.

Keep your ranges exactly the size of the data. A blank date counts as a Saturday in WEEKDAY, so =SUMPRODUCT(--(WEEKDAY(A2:A16,2)=6)), with the empty row 16 included, counts 4 Saturday rows instead of 3. The sum is unaffected because the blank row has no amount, but counts and averages go wrong. Turning the data into a table (Ctrl+T) keeps ranges the right size as you add rows.