Calculate Monthly Excel Totals with EOMONTH and SUMIFS
Question details
Calculate transaction totals for each month when dates and amounts are stored in separate columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has transaction dates in one column and corresponding amounts in another.
- Observed behavior
- The user wants a monthly total for the transactions, using a formula or a grouped summary.
Check that the date column contains real spreadsheet dates and the amount column contains numbers. The examples below use dates in A2:A100, amounts in B2:B100, and a date in the month to summarize in D2.
Use EOMONTH and SUMIFS to total a selected month
Use this formula when you want a monthly total that updates as the source amounts or selected month change.
In D2, enter any valid date in the month you want to total, such as 1/15/2025.
In E2, enter =SUMIFS($B$2:$B$100,$A$2:$A$100,">="&EOMONTH(D2,-1)+1,$A$2:$A$100,"<"&EOMONTH(D2,0)+1). Adjust the ranges to match your data.
Enter dates for other months in D3 and below, then drag the fill handle from E2 down to copy the formula.

Group transaction dates by month in a PivotTable
Use a PivotTable to summarize multiple months without writing a formula.
Calculate Monthly Totals in WPS Spreadsheets
Use EOMONTH and SUMIFS in WPS Spreadsheets to total transactions for a selected month, then copy the formula for additional months.
- 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
- 2. Enter a month date: With dates in A2:A100 and amounts in B2:B100, enter any date in the target month in D2.
- 3. Add the formula: In E2, enter =SUMIFS($B$2:$B$100,$A$2:$A$100,">="&EOMONTH(D2,-1)+1,$A$2:$A$100,"<"&EOMONTH(D2,0)+1). Adjust the ranges if necessary.
- 4. Repeat for other months: Enter additional month dates below D2 and drag the fill handle from E2 down to calculate their totals.

Frequently Asked Questions
Why does the formula use the first day of the next month?
The criterion "<"&EOMONTH(D2,0)+1 excludes the next month while including all times on the last day of the selected month.
Why is my monthly total zero?
Check that D2 and the dates in column A are real dates rather than text, and that column B contains numbers. Also check that the SUMIFS ranges cover the transaction rows.
Can I use MONTH with SUMPRODUCT instead?
Yes, but a MONTH-only comparison combines the same month across all years. Include a YEAR comparison as well if your data spans more than one year.
How do I keep different years separate in a PivotTable?
Right-click a date in the PivotTable, choose Group, and select both Months and Years. This distinguishes, for example, January 2024 from January 2025.




