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.
The Data
| A | B | C | |
|---|---|---|---|
| 1 | Date | Category | Amount |
| 2 | 1/3/2025 | Sales | $4,200 |
| 3 | 1/5/2025 | Returns | -$150 |
| 4 | 1/8/2025 | Sales | $3,800 |
| 5 | 2/1/2025 | Sales | $5,100 |
| 6 | 2/4/2025 | 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/2025 to “2025-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 2025 | $8,000 | -$150 | $7,850 |
| 3 | Feb 2025 | $5,100 | -$200 | $4,900 |
| 4 | Mar 2025 | $11,400 | -$320 | $11,080 |
The Sales formula for January:
=SUMIFS(Data!C:C, Data!B:B, "Sales", Data!A:A, ">="&DATE(2025,1,1), Data!A:A, "<"&DATE(2025,2,1))
Sums all amounts where Category is "Sales" AND the date falls within January 2025.
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.
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.
Start a Project