To sum values by month in Excel, use SUMIFS with a start date for the month and an exclusive start date for the next month. With transaction dates in A2:A100, amounts in B2:B100, and any date in the month to summarize in E2, enter =SUMIFS($B$2:$B$100,$A$2:$A$100,">="&DATE(YEAR(E2),MONTH(E2),1),$A$2:$A$100,"<"&EDATE(DATE(YEAR(E2),MONTH(E2),1),1)). This approach handles months of different lengths and separates the same month in different years.
How the monthly SUMIFS formula works
SUMIFS adds values only when their corresponding rows meet every condition you specify. In this example, the first range is the amounts to add, and the date range is checked twice: once to include dates on or after the first day of the target month, and again to exclude dates on or after the first day of the following month.
The formula builds those boundaries from E2. DATE(YEAR(E2),MONTH(E2),1) returns the first day of E2’s month. EDATE(...,1) advances that date by one month. The less-than condition for the next month is deliberate: it includes every time on the final day of the target month, even if a cell contains a timestamp such as 11:30 p.m. Using a less-than-or-equal condition against the last day can accidentally miss timestamps later that day.
E2 does not have to be the first of the month. If it contains June 18, 2025, the formula still calculates June’s total. Format E2 as a month and year if you want it to display as “Jun 2025” while retaining a real date value.
Prepare the source data
Keep each transaction on its own row, with a date in one column and the numeric value to add in another. For example, a sales list might use column A for Order Date and column B for Sales. A row should represent one transaction, rather than a month total typed into a separate summary table.
- Use genuine Excel dates in the date column, not text that merely looks like a date.
- Keep the dates and values in matching rows, with no shifted or mismatched ranges.
- Check that amounts are numbers. Values stored as text may not be added as expected.
- Include every relevant row in the formula ranges. Using whole-column ranges is possible, but bounded ranges or an Excel Table can be easier to audit.
To check whether a date-looking cell is stored as a number, select it and change its number format temporarily to General. A real Excel date displays as a serial number; text generally remains text. The display format itself does not change the stored date. For a substantial imported dataset, use Excel’s data-cleaning tools or correct the source rather than relying on a visual date format to convert text.
Worked example: total June transactions
Suppose A2:A5 contains June 2, June 15, June 30, and July 1, 2025, while B2:B5 contains 120, 80, 50, and 40. Put June 1, 2025, in E2. With those dates in A2:A5 and amounts in B2:B5, the same SUMIFS pattern adjusted to rows 2 through 5 is =SUMIFS($B$2:$B$5,$A$2:$A$5,">="&DATE(YEAR(E2),MONTH(E2),1),$A$2:$A$5,"<"&EDATE(DATE(YEAR(E2),MONTH(E2),1),1)). It returns 250: 120 + 80 + 50. The July 1 transaction is outside June’s date interval, so its 40 is excluded.
To summarize another month, put a date from that month in E3, E4, and so on, then copy the formula down. The dollar signs keep the source ranges fixed while the reference to E2 changes to E3, E4, and subsequent rows. You can also copy the formula across a summary table if each month’s date is in the corresponding header cell; adjust the reference so it points to that header.
Build a month-by-month summary with a helper column
A helper column can make monthly reporting easier to inspect, especially when you want to filter or sort transactions by month. In C2, beside the transaction date in A2, enter a date representing the first of that transaction’s month. With the date in A2, use =DATE(YEAR(A2),MONTH(A2),1). For a transaction dated June 15, 2025, this returns June 1, 2025. Fill the formula down the column.
Format the helper dates as “mmm yyyy” so they display as Jun 2025, Jul 2025, and so forth. The cells remain dates, not text labels, which makes sorting chronological and filtering more reliable. You can then make a separate list of month-start dates and use SUMIFS to add rows whose helper date equals each listed month. This setup is transparent: the helper column shows which reporting month each transaction belongs to.
For a reusable dataset, convert the source range into an Excel Table: select a cell in the data and choose Insert > Table, or press Ctrl+T on Windows. On Mac, use Command+T. Tables expand when new rows are added and can make formulas easier to read through named columns. Check that the table includes headers and that new transactions are entered as table rows so summaries can include them.
Use a PivotTable to summarize months
A PivotTable is a practical alternative when you want a quick summary, totals by category, or an interactive report rather than a formula-driven result. Select a cell in the source data and choose Insert > PivotTable. Choose where to place the PivotTable, then drag the date field to Rows and the amount field to Values. If Excel displays a count instead of a sum, open the value field settings and choose Sum; text-formatted amounts are a common reason Excel defaults to Count.
If the PivotTable initially lists individual dates, right-click a date in the PivotTable and choose Group. Select Months; select Years as well if the data covers more than one year. Grouping only by month can combine January 2024 and January 2025 into one January total, which is misleading for a year-over-year report. Excel for Mac has PivotTable tools too, though the position and wording of some controls can vary by release. If dates cannot be grouped, inspect the source for blanks, text dates, or invalid entries, then refresh the PivotTable after correcting the data.
A PivotTable is convenient for exploration, but it is not the same as a live formula in every cell. New source rows may require a refresh, available from the PivotTable Analyze tab with Refresh. A Table-based source can make it easier to include appended records, but refresh the PivotTable to update its displayed results.
Choose between SUMIFS, a helper column, and a PivotTable
Use SUMIFS when a report needs a particular monthly total in a fixed cell, when another formula will use that result, or when you want the criteria visible in the worksheet. It works well for a small summary table and does not require grouping a report manually.
Use a helper month column when users need to see or filter each record’s reporting month, or when the same month assignment supports several reports. Use a PivotTable when you want to rearrange fields, compare months across categories, or explore a large dataset without writing a separate formula for every breakdown.
- SUMIFS: direct, repeatable totals with clearly defined date boundaries.
- Helper column: visible month classification that can support filters and other summaries.
- PivotTable: flexible summaries and comparisons, with a refresh step when source data changes.
For a report with categories such as Region or Product, SUMIFS can apply additional criteria alongside the date boundaries. Keep the sum range and all criteria ranges the same size. A PivotTable may be faster to set up if you need totals for many category combinations, while formulas can be more suitable when the report layout must remain fixed.
Common problems and how to fix them
The total is zero or too small. First confirm that the source dates are real dates and the amounts are numeric. Check that the formula ranges cover the full dataset and that the month cell E2 contains the intended year. A cell that displays “Jun 2025” could still contain a different underlying date, so inspect its value in the formula bar.
Dates include times. The formula’s upper boundary is the first day of the following month with a less-than test. This safely includes timestamps throughout the last day. If you instead compare dates to a month-end date using “less than or equal,” entries with times on that date may be left out.
January totals combine across years. A criterion based only on the month number or a text label such as “January” does not distinguish years. The date-boundary formula uses both the year and month from E2. For PivotTables, group by Years as well as Months.
The formula shows an error. Confirm that the ranges have matching dimensions and that parentheses and quotation marks are intact. The examples use commas between function arguments, as in standard English-language Excel settings. Some regional settings use semicolons as separators; Excel may adjust formula separators to match the computer’s locale.
Sorting or grouping behaves oddly. Text dates are not equivalent to Excel date values, even when they look identical on screen. Correct or convert the source data consistently before building the summary. Also check for blank or invalid cells if PivotTable grouping is unavailable.
Frequently Asked Questions
Can Excel sum by month and year?
Yes. Use a date interval that starts on the first day of the target month and ends just before the first day of the next month, as in the SUMIFS formula above. Since both boundaries are built from a full date, January 2025 and January 2026 produce separate totals.
Can I sum by month when the dates include times?
Yes. The formula uses a less-than comparison against the first moment of the next month, so timestamps on the last day are included. This is more reliable than testing whether a timestamp is less than or equal to a date value for the month’s final day.
Can I total a month using a name like “June”?
You can, but a month name alone does not identify a year and may combine transactions from different years. For accurate reporting, use real dates for the month boundaries or include both year and month in a helper field.
Why does my monthly total not change when I add rows?
The new rows may fall outside fixed formula ranges, or a PivotTable may not have been refreshed. Expand the formula’s ranges or use an Excel Table as the source. For a PivotTable, refresh it after adding data and confirm that its source includes the new rows.
Is SUMIF enough to sum by month?
SUMIF applies one criterion, while monthly totals usually need a lower and upper date boundary. SUMIFS is a natural fit because it accepts both conditions. A helper column that assigns each row to a month can also make a single-criterion summary possible, but it requires maintaining that extra column.
Related
- Is Excel Free? Microsoft 365 Plans and Free Options
- Excel Value Formatting: What It Changes and How to Use It
- Text to Excel Converter: Import Text Into Columns
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Validation Custom Formula: Rules and ExamplesIn Excel for Microsoft 365 on Windows, select the cells to control, choose Data > Data Validation, set…
- Excel LEFT Function: Extract Text from the Left SideTo extract characters from the beginning of a cell in Excel, use the LEFT function. If A2 contains…
- Excel Time Difference in Minutes: Formulas and TipsTo calculate the minutes between a start time in A2 and an end time in B2, enter =(B2-A2)*1440…
- How to Add Footnotes in a Word Table the Right WayTo add a true footnote to a Word table, click in the table cell where the reference belongs,…
- LEFT, RIGHT and MID Formulas in Excel: How to Use ThemUse LEFT to return characters from the start of a text value, RIGHT to return them from the…
- How to Calculate Row Totals in Excel with SUM FormulasTo calculate a row total in Excel, add a SUM formula in the cell where you want the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.