Average by Day of Week

Excel Formulas › Average

All versionsSUMPRODUCT

What’s the typical Monday look like? Average a value across all rows that fall on a given weekday with SUMPRODUCT and WEEKDAY — no helper column needed.

Quick formula: average values where the date is a Monday (2):
=SUMPRODUCT((WEEKDAY(dates)=2)*values) / SUMPRODUCT(--(WEEKDAY(dates)=2))
The first SUMPRODUCT sums Monday values; the second counts Mondays; the ratio is the average.

Functions used (tap for the full reference guide):

SUMPRODUCTWEEKDAY

The example

Average sales for each weekday.

AB
1DateMon avg
2Mon 6/1= ratio

The formula

The formula:

=SUMPRODUCT((WEEKDAY(d)=2)*v) / SUMPRODUCT(--(WEEKDAY(d)=2)) // sum ÷ count

How it works

How it works:

  1. WEEKDAY(dates)=2 builds a TRUE/FALSE array marking Mondays (2 = Monday by default).
  2. The numerator SUMPRODUCT((WEEKDAY=2)*values) totals the values on Mondays.
  3. The denominator SUMPRODUCT(--(WEEKDAY=2)) counts how many Mondays there are.
  4. The ratio is the average for that weekday — change the 2 for other days.

Watch the WEEKDAY numbering. By default Sunday = 1 and Monday = 2. Add a second argument to shift it: WEEKDAY(date, 2) makes Monday = 1 through Sunday = 7. Keep your comparison number consistent with the scheme you chose.

Try it: interactive demo

Live demo

Pick a weekday for the sample data.

Average:

Variations

Sum by weekday

Total instead:

=SUMPRODUCT((WEEKDAY(d)=2)*v)

AVERAGEIFS + helper

With a weekday column:

=AVERAGEIFS(v, wd, 2)

Weekdays vs weekends

Mon–Fri:

=AVERAGEIFS(v, wd, ">=2", wd, "<=6")

Pitfalls & errors

Numbering scheme. Default WEEKDAY has Sunday = 1; confirm before comparing to 2 for Monday.

Divide-by-zero. If no rows match the weekday, the ratio errors — wrap in IFERROR.

Real dates required. WEEKDAY needs date serials, not text that looks like a date.

Practice workbook

📊
Download the free Average by Day of Week practice workbook
An average-by-weekday sheet with the sum, AVERAGEIFS, and weekday-vs-weekend variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I average values by day of the week in Excel?
Use =SUMPRODUCT((WEEKDAY(dates)=2)*values)/SUMPRODUCT(--(WEEKDAY(dates)=2)) for Mondays. Change the 2 for other weekdays.
How does WEEKDAY number the days?
By default Sunday is 1 and Saturday is 7. Use WEEKDAY(date, 2) to make Monday 1 through Sunday 7.
Can I use AVERAGEIFS instead?
Yes, if you add a helper column with =WEEKDAY(date), then =AVERAGEIFS(values, weekday_col, 2).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Sum by weekday · Weekday name · Average by group

Function references: SUMPRODUCT · WEEKDAY