Sum Excel Table Amounts by Month with SUMIF or a PivotTable
Question details
The user needs to total transaction amounts by month from an Excel table using SUMIF or a PivotTable.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The worksheet contains a table with Transaction Date and Amount columns, and the user wants monthly totals.
- Observed behavior
- Using MONTH(Table8[Transaction Date]) directly as the SUMIF criteria range does not work because MONTH returns an array rather than a worksheet range.
Confirm that the Transaction Date column contains real Excel dates, not text values, and that the Amount column contains numeric values. Decide whether you need formula-based monthly totals or a refreshable summary report.
Add a Month column and use SUMIF for monthly totals
Use this method when you want monthly totals calculated with worksheet formulas alongside your Excel table.
SUMIF requires its criteria range to be an actual worksheet range. Because MONTH(Table8[Transaction Date]) returns an array, create a calculated Month column in the table and use that column as the criteria range.
In Table8, add a new column named Month. In the first data cell of that column, enter =MONTH([@[Transaction Date]]) and press Enter to fill the calculated column down the table.
In a summary cell, enter a month number from 1 to 12, such as 1 for January or 6 for June.
In the adjacent total cell, enter =SUMIF(Table8[Month],A2,Table8[Amount]), replacing A2 with the cell that contains the month number.
Select the total cell and use Home > Number Format to apply Currency, Accounting, or another number format appropriate for the Amount values.
Create a PivotTable and group transaction dates by month
Use this method when you want an interactive monthly summary that can be filtered, expanded, and refreshed as source data changes.
Calculate Monthly Transaction Totals in WPS Spreadsheets
WPS Spreadsheets lets you add a Month helper column for SUMIF calculations or build a PivotTable to summarize transaction amounts by month. Its familiar spreadsheet interface makes it easy to import an Excel table and continue working with formulas and reports.
- 1. Open the Excel workbook: In WPS Office, select Open and choose the .xlsx or .xls workbook containing the transaction table.
- 2. Add a Month column: Add a table column named Month, then enter =MONTH([@[Transaction Date]]) in its first data cell.
- 3. Create the SUMIF total: In a summary area, enter =SUMIF(Table8[Month],A2,Table8[Amount]), where A2 contains the required month number.
- 4. Build a PivotTable instead: Select a cell in the source table, choose Insert > PivotTable, then place Transaction Date in Rows and Amount in Values.
- 5. Group dates and refresh results: Right-click a PivotTable date, choose Group, select Months, and refresh the PivotTable whenever new transactions are added.
Frequently Asked Questions
Why does MONTH(Table8[Transaction Date]) not work as the criteria range in SUMIF?
MONTH(Table8[Transaction Date]) calculates an array of month numbers, while SUMIF requires a real worksheet range for its first argument. Add a Month column to the table and use Table8[Month] as the criteria range.
What formula totals an Excel table Amount column for one month?
After adding a Month helper column, use =SUMIF(Table8[Month],A2,Table8[Amount]). Put the month number, such as 1 for January, in A2.
How do I avoid combining the same month from different years?
Add a Year column with =YEAR([@[Transaction Date]]) and include a year condition, or create a helper column with =TEXT([@[Transaction Date]],"yyyy-mm") for distinct month-year values.
How do I group dates by month in an Excel PivotTable?
Create a PivotTable, drag Transaction Date to Rows and Amount to Values, then right-click a date in the PivotTable and select Group > Months. Select Years as well when the data includes multiple years.
Why is my PivotTable counting Amount values instead of summing them?
In the PivotTable Fields pane, open the settings for Amount in the Values area and select Value Field Settings > Sum. Ensure the source Amount cells are stored as numbers, not text.
