How to calculate the average per month in Excel
Formula (Excel and Google Sheets)
=AVERAGEIFS($B$2:$B$12,$A$2:$A$12,">="&D2,$A$2:$A$12,"<="&EOMONTH(D2,0))Use AVERAGEIFS with a date window for each month: =AVERAGEIFS($B$2:$B$12,$A$2:$A$12,">="&D2,$A$2:$A$12,"<="&EOMONTH(D2,0)).
- Result on the example
- one result per row in
E2, filled down toE4 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2, filled down to E4. Change any input and the result updates, or edit the formula to experiment.
How it works
Each month is a window from its first day (column D) to EOMONTH(D2,0), its last day. AVERAGEIFS averages only the orders inside the window. The orders on January 31 and March 1 land in the right months, and the locked ranges stay put as the month moves down.
=AVERAGEIFS($B$2:$B$12,$A$2:$A$12,">="&D2,$A$2:$A$12,"<="&EOMONTH(D2,0))Result104.9375
$B$2:$B$1211 rows × 1 col: $84.50, $126.00, $97.25, $112.00, …A range: 11 cells in one column.
$A$2:$A$1211 rows × 1 col: 1/4/2026, 1/11/2026, 1/23/2026, 1/31/2026, …A range: 11 cells in one column.
D2Jan 2026The value in D2.
">="&D2">=46023"The & operator joins as text.
EOMONTH(D2,0)1/31/2026 (46053)EOMONTH: Last day of the month, months away.- start_date
D2Jan 2026 - months
0
- start_date
"<="&EOMONTH(D2,0)"<=46053"The & operator joins as text.
AVERAGEIFS($B$2:$B$12,$A$2:$A$12,">="&D2,$A$2:$A$12,"<="&EOMONTH(D2,0))104.9375AVERAGEIFS: Average of values that meet every condition.- average_range
$B$2:$B$1211 rows × 1 col: $84.50, $126.00, $97.25, $112.00, … - criteria_range1
$A$2:$A$1211 rows × 1 col: 1/4/2026, 1/11/2026, 1/23/2026, 1/31/2026, … - criteria1
">="&D2">=46023" - criteria_range2
$A$2:$A$1211 rows × 1 col: 1/4/2026, 1/11/2026, 1/23/2026, 1/31/2026, … - criteria2
"<="&EOMONTH(D2,0)"<=46053"
- average_range
In Google Sheets
The same formula works in Google Sheets: =AVERAGEIFS($B$2:$B$12,$A$2:$A$12,">="&D2,$A$2:$A$12,"<="&EOMONTH(D2,0)). 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 |
|---|---|---|
| =AVERAGE(FILTER($B$2:$B$12,TEXT($A$2:$A$12,"yyyy-mm")=TEXT(D2,"yyyy-mm"))) | $104.94, $104.73, $99.38 | Excel 365 and Google Sheets: compares each date's year and month as text. |
Common mistakes
- Averaging the monthly totals is a different number from the average order. Decide whether you want the average per order or the average month.
- A month with no orders returns #DIV/0!. Wrap it in IFERROR to show 0 or a dash.
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.
Calculate the average per month
E2, average the order values for the month that starts on the date in D2, then fill down to E4.Questions
How do I get the average monthly total?
Total each month with SUMIFS first, then average those totals.
How do I average by month name across years?
Compare MONTH only: =AVERAGE(FILTER($B$2:$B$12,MONTH($A$2:$A$12)=1)) averages every January.
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.