How to Sum by Month in Excel With SUMIFS

To sum by month in Excel, list the first day of each month and use SUMIFS to add every amount dated from that day to the end of the month. EOMONTH finds the month end for you, so one formula works for 28, 30 and 31-day months alike. This guide builds a monthly summary step by step, then shows two other methods: a helper column and a PivotTable.

The sample data#

Every example uses this list of sales by date in A1:B8. The dates are real Excel dates, not text:

The monthly totals you should get are: January 950, February 650 and March 550. Together they make 2150, the total of the Amount column. That is a handy check once the summary is built.

Step 1: List the months as dates#

Type the first day of each month in a column, as a real date. The safest way is a formula, because typed dates depend on your regional date order. In D2, type =DATE(2024,1,1). In D3, type =EDATE(D2,1) and fill it down: EDATE adds one month on each row, giving 1 Feb 2024, 1 Mar 2024 and so on.

To make the cells show "Jan 2024" instead of a full date, select them and press Ctrl+1 (Mac: ⌘1), choose Custom, and type mmm yyyy. The cell still holds a date, which is what the formula needs.

Don't type the month as text, like "Jan". SUMIFS can't compare the text "Jan" with a date, so you'd get 0.

Step 2: Write the SUMIFS formula#

SUMIFS adds the numbers that meet every condition you give it. For one month, you need two conditions on the date column: on or after the 1st, and on or before the last day. In E2, type:

=SUMIFS($B$2:$B$8,$A$2:$A$8,">="&D2,$A$2:$A$8,"<="&EOMONTH(D2,0))

Here is what each part does:

EOMONTH(D2,0) returns the last day of the month that D2 is in. The 0 means "this month"; 1 would mean next month. The & joins each operator to a date, so Excel builds criteria like ">=45292" behind the scenes.

The result in E2 is 950 (400 + 250 + 300). The 31 January sale is included because of the <=. See the SUMIFS function guide and the EOMONTH function guide for every argument.

Step 3: Fill down for every month#

Select E2:E4 and fill down (Windows: Ctrl+D; Mac: ⌘D). Because the ranges are locked with $ and D2 is not, each row reads its own month:

The totals add up to 2150, matching the Amount column. If they don't, some dates fall outside your month list, or some are stored as text. The same pattern with COUNTIFS counts rows per month instead; see how to count by month.

When dates include times#

If your dates come from a system that adds a time, such as 31 Jan 2024 15:20, the formula above misses those last-day rows. 31 January at 15:20 is later than 31 January at midnight, which is what EOMONTH(D2,0) returns. Use "less than the first day of next month" instead:

=SUMIFS($B$2:$B$8,$A$2:$A$8,">="&D2,$A$2:$A$8,"<"&EOMONTH(D2,0)+1)

EOMONTH(D2,0)+1 is 1 February, and < keeps everything before it, whatever the time. This version works for dates with or without times, so it is a safe habit.

Other ways to sum by month#

SUMIFS with a month list is the most flexible method, but there are two other common approaches.

A helper column. Add a column that turns each date into a month label, such as =TEXT(A2,"yyyy-mm"), which gives "2024-01". Then sum by that label with =SUMIF(C2:C8,"2024-01",B2:B8). This is easy to read, and it is the same as summing by any category; see how to sum by category. The downside is the extra column.

SUMPRODUCT with MONTH and YEAR. This works without a month list:

=SUMPRODUCT((MONTH(A2:A8)=1)*(YEAR(A2:A8)=2024)*B2:B8)

It returns 950. Always include the YEAR test. With MONTH alone, January 2023 and January 2024 would be added together. Note that =SUMIF(MONTH(A2:A8),1,B2:B8) doesn't work, because SUMIF needs a range of cells, not a calculated list.

A PivotTable. Select the data, go to Insert > PivotTable, put Date in Rows and Amount in Values. Recent versions of Excel group the dates by month automatically; if not, right-click a date and choose Group, then Months and Years. This needs no formulas, but it doesn't update until you refresh it.

Why a sum by month formula returns 0#

If a month shows 0 or too little, check these:

1. Months typed as text. "Jan" is not a date. Use the 1st of the month as a real date.
2. Dates stored as text. Imported dates often sit on the left of the cell, which shows they are text. Convert them with DATEVALUE or Data > Text to Columns.
3. A cell reference inside the quotes. Write ">="&D2, not ">=D2".
4. Dates with times. Use the "<"&EOMONTH(D2,0)+1 version.
5. Ranges not locked. Without $, the ranges slide down as you fill and skip rows.

For any date range, not just whole months, read how to sum by date range. To add more conditions, such as one region per month, add another pair to SUMIFS; the guide to COUNTIFS and SUMIFS with multiple conditions shows how, and SUMIFS with OR logic covers either-or conditions.

Practise it#

Monthly totals are one of the most common tasks in finance and operations tests. Practise dates in criteria in a live spreadsheet, then build multi-condition totals in the COUNTIFS and SUMIFS lesson. Each lesson checks your formula instantly. To test yourself under time pressure, try the finance Excel test.