Date & Time
Sum and average by calendar month across years
Group dated observations by month and compare a group average with the overall sample average.
Pool multi-year history by calendar month (1–12) to measure seasonality!
Standard date-range SUMIFS(">="&StartOfMonth, "<="&EndOfMonth) isolates one specific month in one specific year (e.g., Dec 2026). When forecasting retail holiday surges or summer dips, however, FP&A analysts want to pool ALL Decembers across 2024, 2025, and 2026 ignoring the year!
A MONTH helper lets SUMIFS and AVERAGEIFS group by calendar month across years. Here the ratio is a descriptive group mean divided by the mean of all six observations. Unequal coverage and trends can bias this ratio; it is not a fitted seasonal forecast or proof of a recurring effect. For monthly-total seasonality, first aggregate complete comparable months and account for trend and missing periods.
| Calendar Month | Pooled Multi-Year Entries ($k) | Pooled Sum & Average | Seasonality Index (vs. $155k Mean) |
|---|---|---|---|
| Month 12 (Dec 2024, 2025, 2026) | 180, 210, 240 | Sum = $630k | Avg = $210k | 210 / 155 = 1.3548 (Peak +35.5%) |
| Month 1 (Jan 2025, 2026) | 90, 110 | Sum = $200k | Avg = $100k | 100 / 155 = 0.6452 (Dip -35.5%) |
| Month 7 (Jul 2026) | 100 | Sum = $100k | Avg = $100k | 100 / 155 = 0.6452 |
Sum revenue for a calendar month (1–12) across all years
B2:B7 stores =MONTH(A2) and E2 holds target month number (12 for December).
Use real date values or DATE formulas; text-date parsing can depend on locale. The ranges are fixed to the six example rows. Target month must be an integer 1 through 12; all six values must be numeric, including zeros.
=IF(OR(NOT(ISNUMBER(E2)),E2<1,E2>12,E2<>INT(E2),COUNT(C2:C7)<>6),"Invalid inputs",SUMIFS(C2:C7, B2:B7, E2))Group mean relative to the sample mean
Compares the target calendar month's multi-year average against the overall sample average.
=IF(OR(NOT(ISNUMBER(E2)),E2<1,E2>12,E2<>INT(E2),COUNT(C2:C7)<>6),"Invalid inputs",IF(COUNTIF(B2:B7,E2)=0,"No observations",IF(AVERAGE(C2:C7)=0,"Zero baseline",ROUND(AVERAGEIFS(C2:C7,B2:B7,E2)/AVERAGE(C2:C7),4))))Try the example
Use the original values or change them to test your understanding.