Function Problems

Calculate Monthly Excel Totals with EOMONTH and SUMIFS

Huda QurayshiHuda Qurayshi Nov 22, 2026 868 views

Question details

Calculate transaction totals for each month when dates and amounts are stored in separate columns.

Calculate Monthly Excel Totals with EOMONTH and SUMIFS
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter a date for the month

In D2, enter any valid date in the month you want to total, such as 1/15/2025.

2
Enter the monthly total 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 to match your data.

3
Calculate additional months

Enter dates for other months in D3 and below, then drag the fill handle from E2 down to copy the formula.

Use EOMONTH and SUMIFS to total a selected month
How the date limits work: The formula includes dates from the first day of the selected month up to, but not including, the first day of the next month. It also includes timestamps on the last day.
Calculate monthly totals in WPS

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. 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
  2. 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. 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. 4. Repeat for other months: Enter additional month dates below D2 and drag the fill handle from E2 down to calculate their totals.
Calculate month-specific totals with EOMONTH and SUMIFSOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interfaceWork with a free Office suite
microsoft office alternative - wps office

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.