Excel Sum Between Dates: SUMIFS Formulas and Examples

If dates are in A2:A100, amounts are in B2:B100, and the start and end dates are in E2 and F2, use =SUMIFS($B$2:$B$100,$A$2:$A$100,">="&E2,$A$2:$A$100,"<="&F2) to add amounts on or between those dates. For records that include times as well as dates, use the time-safe version described below so entries later on the end date are included.

How SUMIFS adds values between dates

SUMIFS is designed to add values that meet multiple conditions. In this case, the amount range is the set of numbers Excel should total, while the date range is checked twice: once against the start date and once against the end date. The two conditions together create a date interval.

The criteria operators matter. The text ">=" means “greater than or equal to,” so a transaction on the start date qualifies. The text "<=" means “less than or equal to,” so a transaction on the end date qualifies too. The ampersand joins each operator to the date stored in E2 or F2. Without that joining step, Excel would not evaluate the cell value as part of the criteria string.

SUMIFS uses the syntax SUMIFS(sum_range, criteria_range1, criteria1, ...). Each criteria range must line up with the sum range: they should cover the same number of rows and correspond to the same records. In the example above, A2:A100 and B2:B100 both cover rows 2 through 100. If the ranges are mismatched, the result can be wrong or Excel can return an error.

Arrange the date and amount data

A reliable formula begins with a tidy source list. Put one record on each row, keep the date values in one column, and keep the numbers to be totaled in another. Include a clear header row, and avoid merged cells or subtotal rows inside the list. For example, a simple transaction table might have Date in column A, Description in column B, and Amount in column C.

Excel must recognize the date entries as dates, not merely text that looks like a date. Dates Excel recognizes are stored as serial numbers and displayed using a date format. You can usually check a cell by changing its number format temporarily to General: a real date displays as a number, while text may remain unchanged. Be cautious when importing data, copying from websites, or working with dates written in a different regional order, such as 04/05/2026, which can mean different days and months in different locales.

Keep the date column consistent: do not mix dates, text labels, and notes in the same range.
Store amounts as numeric values. A currency symbol applied through cell formatting is fine, but an amount imported as text will not sum normally.
Put the start and end dates in separate cells, and format those cells as dates so they are easy to review.
Use a range large enough to include all records, or convert the list to an Excel table so new rows are easier to include in later formulas.

Formatting alone does not turn text into a true date. If Excel imported dates as text, use an appropriate conversion method for the source data before relying on SUMIFS. The correct conversion depends on how the text is arranged and the workbook’s regional settings; blindly changing the display format will not repair the underlying value.

Worked example with an inclusive date range

Suppose A2:A6 contains January 1, January 5, January 10, January 15, and February 1, all in the same year. Suppose the corresponding amounts in B2:B6 are 120, 80, 200, 50, and 300. Set E2 to January 5 and F2 to January 15. The formula below assumes those dates and amounts occupy exactly those cells.

With dates in A2:A6, amounts in B2:B6, the start date in E2, and the end date in F2, use =SUMIFS($B$2:$B$6,$A$2:$A$6,">="&E2,$A$2:$A$6,"<="&F2). The result is 330: Excel includes 80 on January 5, 200 on January 10, and 50 on January 15. It excludes the January 1 and February 1 records.

Dollar signs lock the source ranges if you copy the formula elsewhere. They do not lock E2 and F2, so when copied down, the criteria cells can change to E3 and F3, which is often useful for a list of separate periods. If you want every copied formula to keep using the same start and end cells, lock those references too, as in $E$2 and $F$2.

To enter a date directly in a formula’s criteria, it is safer to use a DATE expression than to type a date string whose interpretation may vary by locale. With dates in A2:A100 and amounts in B2:B100, this formula totals records from January 1 through January 31, 2026, inclusive: =SUMIFS($B$2:$B$100,$A$2:$A$100,">="&DATE(2026,1,1),$A$2:$A$100,"<="&DATE(2026,1,31)). DATE takes year, month, and day as separate numeric arguments, avoiding ambiguity between day-first and month-first text dates.

Include all times on the end date

The basic inclusive formula is appropriate when the date cells contain dates without time values. But Excel can store a date and time together as a serial value with a fractional part for the time. For example, a value on January 15 at noon is later than January 15 at midnight. A criterion of less than or equal to the end date, when F2 is January 15 at midnight, may therefore omit entries later that day.

For a range that may contain timestamps, use a less-than test against the day after the selected end date. This includes every time on the end date but excludes midnight at the start of the following day. With timestamps in A2:A100, amounts in B2:B100, a date-only start in E2, and a date-only end in F2, use =SUMIFS($B$2:$B$100,$A$2:$A$100,">="&E2,$A$2:$A$100,"<"&F2+1).

This boundary pattern is also useful for reports defined as “from the start date through the end date,” regardless of whether the source contains times. It assumes E2 and F2 represent dates at midnight rather than selected time-of-day boundaries. If you need a precise interval, such as including records from 9:00 a.m. on one date through 5:00 p.m. on another, enter actual date-time values for both boundaries and decide whether the ending instant should be inclusive or exclusive.

Use a changing period or a monthly total

Cell-based boundaries make a report reusable. Change E2 or F2, and the formula recalculates for the new period. That is generally more convenient and less error-prone than editing date values inside the formula each time. You can also copy a formula down beside a list of reporting periods, provided each row has its own start and end dates.

For a monthly report, a useful convention is to use the first day of the month as the start and the first day of the next month as an exclusive end. This avoids calculating the number of days in each month and safely handles timestamps. With dates or timestamps in A2:A100, amounts in B2:B100, and the first day of the target month in E2, use =SUMIFS($B$2:$B$100,$A$2:$A$100,">="&E2,$A$2:$A$100,"<"&EDATE(E2,1)). If E2 is January 1, 2026, the criteria include January records and stop before February 1. EDATE advances the date by the specified number of months.

For a year-to-date total, set the start date to the first day of the year and the end boundary to the day after the reporting date, using the same “less than next day” logic when timestamps are possible. Keeping reporting boundaries visible in cells makes it easier to audit the result and explain what a report includes.

Common problems and how to check them

A zero result does not always mean there are no matching transactions. First check whether the source dates are real Excel dates and whether the chosen boundaries fall in the same year as the data. Then confirm that the amounts are numeric, that the formula’s ranges include the records, and that the start date is not later than the end date. A date displayed as 1/5/2026 may be May 1 or January 5 depending on how it was interpreted when entered.

Unexpectedly low totals often come from timestamps at the end of the period. If the source includes times, replace the end-date “less than or equal to” condition with the “less than next day” pattern. Another frequent cause is a range that stops above newly added rows. Expanding the ranges or using a table can prevent new records from being left out.

If Excel shows the formula itself in the cell instead of a result, check whether the cell is formatted as Text or whether Show Formulas is enabled. Change a text-formatted formula cell to General, then re-enter the formula. On Windows, Ctrl+` toggles Show Formulas; on Mac, the equivalent shortcut may depend on the keyboard and Excel version, so the Show Formulas command on the Formulas tab is a reliable alternative.

If Excel reports a problem with the formula syntax, check for unmatched parentheses, straight quotation marks around criteria operators, and commas between arguments. Some installations configured for a different regional list separator may require semicolons when formulas are entered, even though comma separators are shown in this article. Also check that all SUMIFS ranges have the same dimensions.

SUMIFS compared with other ways to total dates

For most date-range totals, SUMIFS is the clearest choice because its two boundary conditions are visible in the formula and it works directly with ordinary ranges. SUMIF supports one condition, so it is not as direct for a bounded interval unless you add a second calculation or use another method.

A PivotTable is a good alternative when the goal is to explore totals by month, quarter, year, category, or other groups without building a separate formula for every period. Select the data and choose Insert > PivotTable, then place the date field in Rows and the amount field in Values. Depending on the data and Excel version, dates can be grouped through the PivotTable options. A PivotTable is interactive, but it may need to be refreshed after source data changes, whereas ordinary worksheet formulas recalculate as their inputs change.

Filters can show only rows in a date range, but filtering alone does not create a durable, criteria-driven total. A status-bar sum or a subtotal can be convenient for a quick check, yet a clearly labeled SUMIFS cell is usually easier to reuse and audit in a recurring report. Choose the approach based on whether you need a single calculated answer, a flexible summary, or a quick visual inspection.

Frequently Asked Questions

Can SUMIFS include both the start date and the end date?
Yes. Use a greater-than-or-equal-to condition for the start and a less-than-or-equal-to condition for the end when the source contains date-only values. If timestamps are present, use a less-than condition against the day after the end date to include the full final day.

Can I sum between dates in different years?
Yes. SUMIFS compares the actual date values, including the year, so a start date in one year and an end date in a later year define a continuous interval. Check that both criteria cells contain the intended years; a display format that hides the year can make mistakes harder to spot.

Why does my formula return zero even though dates appear to match?
The dates may be text rather than Excel date values, or the cells may include times that put them outside the criteria. Confirm the underlying dates, inspect whether the data contains timestamps, and verify that the amount range contains numbers and aligns with the date range.

How do I total a month when the month has a different number of days?
Use the first day of the month as the lower boundary and the first day of the next month as an exclusive upper boundary. That approach works for February, leap years, and months of 30 or 31 days without manually finding the last day.

Does changing a date’s display format fix a text date?
No. Number formatting changes how an underlying value appears; it does not convert text into a real date. Convert or re-import the source values appropriately, then apply a date format and verify that the SUMIFS criteria return the expected records.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.