Sum by Date Range or Month in Excel With SUMIFS

A SUMIFS date range is two conditions on the same date column: on or after the start date, and before (or on) the end date. With it you can total sales for a month, a quarter, or any two dates a manager types in. This guide is for anyone building monthly reports or facing a date question in an Excel test, and it shows the exact criteria syntax that trips most people up.

How do dates work in SUMIFS criteria?#

Excel stores every date as a number, so you compare dates with the same operators as numbers: >=, <=, < and >. The rule for writing them:

• Put the operator in quotes: ">=".
• Join the date outside the quotes with &: ">="&DATE(2025,1,1).

DATE(year, month, day) builds a real date. It's safer than typing ">=1/2/2025", because 1/2/2025 means January 2 in the US but February 1 in the UK and many other countries.

Don't write ">=DATE(2025,1,1)". Inside quotes, DATE is just letters, so nothing matches and the result is 0.

The examples use this order table in A1:D9:

The dates are shown as year-month-day here. Your sheet may display them differently, depending on your regional settings.

How to count or sum from a date onwards#

For a one-sided range, such as "on or after Feb 1", use a single condition. To count those orders:

=COUNTIFS(A2:A9,">="&DATE(2025,2,1))

The result is 5 (rows 5 to 9). Use ">" instead and the Feb 1 order drops out, giving 4. The same criteria work in SUMIFS, COUNTIF and SUMIF.

How to sum between two dates with SUMIFS#

To total a date range, list the date column twice: once with the start condition and once with the end condition. For January 2025:

=SUMIFS(D2:D9,A2:A9,">="&DATE(2025,1,1),A2:A9,"<"&DATE(2025,2,1))

The result is 4,970 (2,700 + 950 + 1,320). Here is what each part does:

Why use "<" the 1st of next month?#

"<="&DATE(2025,1,31) gives the same total here, but "<"&DATE(2025,2,1) is the safer habit for two reasons. You never need to know whether a month has 28, 30 or 31 days. And if a date includes a time, such as Jan 31 at 3 PM, Excel stores it as slightly more than Jan 31, so <= would miss it.

How to sum by month with EOMONTH#

To build a monthly summary, put the first day of each month in a column and let EOMONTH find the last day. With Jan 1, Feb 1 and Mar 1, 2025 in F5:F7, type this in G5:

=SUMIFS($D$2:$D$9,$A$2:$A$9,">="&F5,$A$2:$A$9,"<="&EOMONTH(F5,0))

Fill down to G7 (Windows: Ctrl+D; Mac: ⌘D). EOMONTH(F5,0) returns the last day of F5's month, so you get:

The three months add up to 14,460, the same as =SUM(D2:D9). To show the month name instead of a full date in F5:F7, give those cells a custom number format of mmm yyyy; the value stays a real date, so SUMIFS still works.

For a quarter, keep the same pattern and move the end three months on. The first quarter of 2025 runs from Jan 1 up to, but not including, Apr 1:

=SUMIFS(D2:D9,A2:A9,">="&DATE(2025,1,1),A2:A9,"<"&DATE(2025,4,1))

All eight orders fall in Q1, so this also gives 14,460. The data ranges have $ signs so they stay fixed, while F5 moves down to F6 and F7. For a whole year, use ">="&DATE(2025,1,1) and "<"&DATE(2026,1,1).

How to use start and end dates from cells#

Point the criteria at cells so anyone can change the range without touching the formula. Put a region in J1, a start date in J2 and an end date in J3. To count that region's orders with both days included:

=COUNTIFS(B2:B9,J1,A2:A9,">="&J2,A2:A9,"<="&J3)

Swap COUNTIFS for SUMIFS and add the amounts first to get the total. With East, Jan 14 and Feb 18, 2025, =SUMIFS(D2:D9,B2:B9,J1,A2:A9,">="&J2,A2:A9,"<="&J3) gives 950 + 1,320 + 1,800 = 4,070.

You can mix date pairs with any other condition. East sales in February 2025:

=SUMIFS(D2:D9,B2:B9,"East",A2:A9,">="&DATE(2025,2,1),A2:A9,"<"&DATE(2025,3,1))

Only the Feb 1 order matches, so the result is 1,800. For many regions and months at once, a PivotTable grouped by month can be quicker; PivotTable vs SUMIFS compares the two.

For a rolling period, use TODAY. The last 30 days, including today:

=SUMIFS(D2:D9,A2:A9,">="&TODAY()-30,A2:A9,"<="&TODAY())

Why is SUMIFS with dates returning 0?#

A date SUMIFS that returns 0 usually has the date inside the quotes or dates stored as text. Check these:

1. Date or cell inside quotes. ">=J2" and ">=DATE(2025,1,1)" are plain text. Write ">="&J2.
2. Dates stored as text. Imported dates often look right but sit on the left of the cell. Test one with =ISNUMBER(A2): FALSE means text. Convert them with Data > Text to Columns, or =VALUE(A2) in a helper column.
3. Start and end swapped. ">="&end with "<="&start matches nothing.
4. Typed dates read the wrong way. Use DATE instead of "1/2/2025".
5. Ranges of different sizes. Every range must cover the same rows, or you get #VALUE!.

Related guides#

If SUMIFS is new to you, start with how to use SUMIF and COUNTIFS and SUMIFS with multiple conditions. For a quick count of rows that pass one test, COUNTIF criteria examples cover every operator. The SUMIFS function guide has the full syntax. To learn why dates behave like numbers, read why Excel dates are numbers.

Practise it#

Practise dates in criteria in a live spreadsheet: count orders from a date, total January with two conditions, build a monthly summary with EOMONTH, and drive a date range from input cells.