To summarize daily transaction data by month in Excel, use SUMIFS with a first-of-month date and EDATE as the upper boundary: =SUMIFS(Data!C:C, Data!B:B, "Sales", Data!A:A, ">="&A2, Data!A:A, "<"&EDATE(A2,1)). COUNTIFS and AVERAGEIFS take the same arguments to add transaction counts and averages to the same summary.
Your accounting system, POS, or CRM exports daily transaction data: hundreds or thousands of rows. Leadership wants a one-page summary showing totals by month, category, and trend direction. Building this manually every period is tedious. Here’s how to create a monthly summary that updates automatically when new data is added.
Download the finished workbook (.xlsx, free, no sign-up)
Sample daily transaction data
| A | B | C | |
|---|---|---|---|
| 1 | Date | Category | Amount |
| 2 | 1/3/2026 | Sales | $4,200 |
| 3 | 1/5/2026 | Returns | -$150 |
| 4 | 1/8/2026 | Sales | $3,800 |
| 5 | 2/1/2026 | Sales | $5,100 |
| 6 | 2/4/2026 | Returns | -$200 |
| 7 | … hundreds more rows … | ||
Step 1: Extract the Month
Add a helper column to extract the month-year from each date. This makes grouping easier:
=TEXT(A2, "YYYY-MM")
Converts 1/3/2026 to “2026-01”. This creates a consistent grouping key that sorts chronologically.
Alternative
You can also use =EOMONTH(A2, 0) which returns the last day of the month. This gives you a real date that’s easier to format and chart, but TEXT is simpler for summary tables.
Step 2: Build the Summary with SUMIFS
Create a summary table on a separate sheet:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | Returns | Net |
| 2 | Jan 2026 | $8,000 | -$150 | $7,850 |
| 3 | Feb 2026 | $5,100 | -$200 | $4,900 |
| 4 | Mar 2026 | $11,400 | -$320 | $11,080 |
The Sales formula for January:
=SUMIFS(Data!C:C, Data!B:B, "Sales", Data!A:A, ">="&DATE(2026,1,1), Data!A:A, "<"&DATE(2026,2,1))
Sums all amounts where Category is “Sales” AND the date falls within January 2026.
Step 3: Make It Dynamic with Date References
Instead of hardcoding dates, reference your month column so the formula adapts as you copy it down:
If column A contains the first day of each month
=SUMIFS(Data!C:C, Data!B:B, "Sales", Data!A:A, ">="&A2, Data!A:A, "<"&EDATE(A2,1))
Uses EDATE to automatically calculate the first day of the next month. Copy down for all months.
Step 4: Add Transaction Count and Average
For a complete summary, add count and average columns:
Transaction count
=COUNTIFS(Data!B:B, "Sales", Data!A:A, ">="&A2, Data!A:A, "<"&EDATE(A2,1))
Average transaction
=AVERAGEIFS(Data!C:C, Data!B:B, "Sales", Data!A:A, ">="&A2, Data!A:A, "<"&EDATE(A2,1))
Alternative: Pivot Tables
For a quick summary without formulas, Pivot Tables are hard to beat:
1Select your data and go to Insert → PivotTable
2Drag Date to Rows (Excel auto-groups by month)
3Drag Category to Columns
4Drag Amount to Values
SUMIFS vs Pivot Tables
Use SUMIFS when you need a fixed-layout report that always looks the same and auto-updates as data is added. Use Pivot Tables when you need to explore the data interactively and change groupings on the fly. For automated reporting that gets emailed to stakeholders, SUMIFS is usually the better foundation.
If the transactions are invoices you send, EG Quote & Invoice keeps this summary for you: its Reports page shows document counts and amounts by month, plus a revenue and tax summary.
Common questions
Should I use SUMIFS or a Pivot Table?
A Pivot Table is faster to build and better for exploring. SUMIFS is better when the report layout is fixed and must look the same every month, because the formulas stay where you put them.
Why does my SUMIFS return zero?
Almost always because the dates are stored as text rather than real dates, or because the criteria text does not match exactly. Check with =ISNUMBER(A2) on a date cell.
How do I make new months appear automatically?
Build the month column from a start date using EDATE, so each row is one month after the last. The SUMIFS formulas then extend by filling down.
When Formulas Aren’t Enough
SUMIFS-based summary tables work well for standard monthly reports. But as reporting requirements grow, formulas hit their limits:
- Multiple data sources: combining daily exports from different systems before summarizing
- Dynamic categories: new product lines or regions appear and the summary needs to expand automatically
- Formatted output: leadership wants charts, branded headers, conditional formatting, and print-ready layouts
- Email delivery: the report needs to be generated and sent automatically on the first of each month
- Year-over-year comparison: adding prior year data alongside current year for trend analysis
A custom VBA report generator consolidates data from multiple sources, builds the summary with any layout, adds charts and formatting, and can run on a schedule with one click.
“If you need your job done on time and accurate, don’t look any further than Excel Gurus! 6 STARS!”
Darryl H. · Repeat client (15+ projects) · PeoplePerHour
Building the Same Report Every Month?
Tell us about your reporting process and we’ll automate it.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.