Monthly Totals From Daily Data in Excel (SUMIFS + EOMONTH, Pivot and GROUPBY)

Monthly Totals From Daily Data in Excel (SUMIFS + EOMONTH, Pivot and GROUPBY) featured illustration

📎 This article includes 1 downloadable practice file ↓

⏱ 3 min read

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
  1. Set up a month list
  2. SUMIFS between month start and month end
  3. Month × region grid
  4. Count orders and average per month
  5. Excel 365: GROUPBY in one formula
  6. Pivot table route
  7. Where people go wrong
  8. Practice
  9. Continue learning

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.

Advertisement

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.

💡 If your dates include times (e.g. 15-Apr 18:30), use "<" & 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.

Advertisement

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.

Advertisement

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.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong

Leave a Comment