# How to calculate the average per month in Excel

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))`.

**Excel:** `=AVERAGEIFS($B$2:$B$12,$A$2:$A$12,">="&D2,$A$2:$A$12,"<="&EOMONTH(D2,0))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Date | Order value |  | Month | Average order |
| 2026-01-04 | 84.5 |  | 2026-01-01 |  |
| 2026-01-11 | 126 |  | 2026-02-01 |  |
| 2026-01-23 | 97.25 |  | 2026-03-01 |  |
| 2026-01-31 | 112 |  |  |  |
| 2026-02-02 | 64.8 |  |  |  |
| 2026-02-14 | 158.4 |  |  |  |
| 2026-02-27 | 91 |  |  |  |
| 2026-03-01 | 73.5 |  |  |  |
| 2026-03-09 | 104.75 |  |  |  |
| 2026-03-18 | 88 |  |  |  |
| 2026-03-31 | 131.25 |  |  |  |

Formula in E2, filled to E4: `=AVERAGEIFS($B$2:$B$12,$A$2:$A$12,">="&D2,$A$2:$A$12,"<="&EOMONTH(D2,0))`

Result: $104.94; $104.73; $99.38

## 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.

## Other ways

- `=AVERAGE(FILTER($B$2:$B$12,TEXT($A$2:$A$12,"yyyy-mm")=TEXT(D2,"yyyy-mm")))`: 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.

## 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.

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/average-per-month