📎 This article includes 1 downloadable practice file ↓
Sales, expenses and attendance arrive one row per day. Reports want one number per month. Getting from one to the other is a core Excel skill, and the classic mistake is grouping by MONTH() alone — which adds April 2026 and April 2027 together.
In this article
Set up a month list
In L2 type the first day of the first month (1-Apr-2026). In L3: =EDATE(L2,1), copied down. Format the column as mmm-yy so it shows Apr-26, May-26… but the cells still hold real dates.
In Excel 365 one formula does it: =EDATE(DATE(2026,4,1), SEQUENCE(12,1,0)).
SUMIFS between month start and month end
=SUMIFS($I$2:$I$201, $A$2:$A$201, ">=" & L2, $A$2:$A$201, "<=" & EOMONTH(L2, 0))“Add the amounts where the date is on or after the first of the month and on or before its last day.” EOMONTH(L2,0) is the month-end, whatever the month’s length. This respects the year automatically.
"<" & EOMONTH(L2,0)+1 instead of "<="&EOMONTH(L2,0), so the last day’s evening entries aren’t missed.Month × region grid
Months down column L, regions across row 1 (M1:P1):
=SUMIFS($I$2:$I$201, $A$2:$A$201, ">="&$L2, $A$2:$A$201, "<="&EOMONTH($L2,0), $B$2:$B$201, M$1)The mixed references ($L2, M$1) let one formula fill the whole grid.
Count orders and average per month
=COUNTIFS($A$2:$A$201, ">="&L2, $A$2:$A$201, "<="&EOMONTH(L2,0))
=AVERAGEIFS($I$2:$I$201, $A$2:$A$201, ">="&L2, $A$2:$A$201, "<="&EOMONTH(L2,0))Excel 365: GROUPBY in one formula
=GROUPBY(TEXT(A2:A201, "yyyy-mm"), I2:I201, SUM)The “yyyy-mm” key sorts correctly and keeps years apart. (GROUPBY is in current Microsoft 365 builds; older versions show #NAME?.)
Pivot table route
Insert a pivot, put Date in Rows — Excel groups by Years, Quarters and Months automatically. Keep Years in the grouping, otherwise months from different years merge. For financial-year grouping, add a helper column (see financial year from a date).
Where people go wrong
| Mistake | Result |
|---|---|
Grouping by MONTH(A2) only |
April of every year added together |
| Month list typed as text “Apr-26” | SUMIFS compares text to dates and returns 0 |
"<=" & L2+30 for month end |
Wrong for 31-day months and February |
| Dates stored as text | Everything returns 0 — check with ISNUMBER |
Practice
The combo practice workbook below has a Sales sheet of 200 orders and a task for every formula on this page. Type your formula in the yellow column; the check turns green when the answer matches. The Answers sheet has working versions.
More combinations: all formula combos · functions used here are explained in the Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.