Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to group data by month using formulas instead of a pivot in Excel and Google Sheets
Topic:Pivot-style summaries (formula approach)Excel & Google Sheets
=SUMIFS(B2:B7,A2:A7,">="&DATE(2024,1,1),A2:A7,"<="&EOMONTH(DATE(2024,1,1),0))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Date | Amount |
| 2024-01-05 | 500 |
| 2024-01-20 | 300 |
| 2024-02-10 | 700 |
| 2024-02-25 | 200 |
| 2024-03-15 | 900 |
| 2024-03-28 | 100 |
=SUMIFS(B2:B7,A2:A7,">="&DATE(2024,1,1),A2:A7,"<="&EOMONTH(DATE(2024,1,1),0))→0
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Place dates in A2:A7 and amounts in B2:B7 in your flat data table.
- 2In a summary table, list the first day of each month in column D (e.g., D2 = DATE(2024,1,1), D3 = DATE(2024,2,1)).
- 3In E2 write =SUMIFS(B$2:B$7,A$2:A$7,">="&D2,A$2:A$7,"<="&EOMONTH(D2,0)).
- 4EOMONTH(D2,0) returns the last day of the month in D2 — this bounds the upper date without hardcoding day counts.
- 5Copy E2 down for each month row; the formula adjusts D2 reference automatically.
Tips
- Use EOMONTH(start,0) rather than adding 30 days — it handles months of different lengths and leap years correctly.
- To group by month-year label, use TEXT(D2,"mmm yyyy") in an adjacent column for readable headers.
- In Google Sheets, wrap the SUMIFS in ARRAYFORMULA to spill results for all months at once from a single cell.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How is this different from grouping in a pivot table?
A pivot's date grouping requires a manual Refresh when data changes and the month list is generated automatically. The SUMIFS approach updates instantly and lets you control exactly which months appear.
Can I group by year and month at the same time?
Yes — add YEAR() and MONTH() helper columns, then SUMIFS on both: =SUMIFS(B2:B7,YEAR(A2:A7),2024,MONTH(A2:A7),3).
What if dates are stored as text?
Use DATEVALUE() to convert text dates to serial numbers before the SUMIFS comparison, or clean the source data with Text to Columns.
More on Pivot-style summaries (formula approach)
See all →Reference
GETPIVOTDATA
Extract a specific value from a PivotTable using field names and item values as explicit identifiers.
How-toHow to build a pivot-style summary with formulas
Build a formula-driven pivot-style cross-tab in Excel or Google Sheets using SUMIFS with mixed references — live totals by row and column without inserting a pivot table.
How-toHow to build a pivot-style summary with QUERY
Use Google Sheets QUERY with GROUP BY, SUM, and LABEL to build a pivot-style summary table without a manual pivot table — Excel alternative uses SUMIFS.
How-toHow to use GETPIVOTDATA to pull values from a pivot table
Learn how GETPIVOTDATA extracts specific values from a pivot table in Excel and Google Sheets, avoiding broken references when the pivot refreshes.
How-toHow to write formulas for a pivot table calculated field
Understand how pivot calculated fields work and when to replace them with SUMIFS-based formulas in Excel and Google Sheets for more control and flexibility.
How-toHow to calculate percent of total without a pivot table
Use SUMIF and SUM to calculate each category's percent of grand total in Excel and Google Sheets — no pivot table required.