Excel SUMIF With a Date Range: Formula and Examples

Formulas & functions · Updated November 19, 2026

For a total between two dates, use SUMIFS, which can apply both a start-date and an end-date condition: with dates in A2:A6, amounts in B2:B6, the start date in D2, and the final date in E2, use =SUMIFS(B2:B6,A2:A6,">="&D2,A2:A6,"<"&E2+1). Although people often call this a SUMIF formula, SUMIFS is the right tool for a two-sided date range; the less-than-next-day test also includes timestamps on the final date.

Why SUMIFS is usually the answer

SUMIF accepts one condition, such as dates on or after a particular day. A date range has two boundaries: the date must be on or after the start, and on or before the end. SUMIFS is designed to add values only when all its criteria are met, so it can check both boundaries against the same date column.

Excel does have a way to express a range by subtracting one SUMIF result from another, but that is harder to read and easier to get wrong. It is generally clearer to use SUMIFS, even when the question or worksheet label says “SUMIF.” The function name is plural because it accepts multiple criteria; the sum itself is still a single total.

In the example above, D2 and E2 should contain actual Excel dates, not date-looking text. The formula includes the start date and every time on the end date by requiring dates to be less than the day after E2. If the source data contains date-only values with no times, a less-than-or-equal-to end condition can also work, but the next-day boundary is more robust.

How the formula works

SUMIFS puts the range to add first, followed by pairs of criteria ranges and criteria. In the example, B2:B6 is the sum range. A2:A6 appears twice because Excel must test that same date range against two separate conditions.

  • ">="&D2 joins the greater-than-or-equal-to operator to the start date in D2.
  • "<"&E2+1 joins the less-than operator to the day after the end date in E2.
  • Rows that meet both conditions contribute their corresponding value from column B to the total.

The comparison operators are text, while the cell references supply the date values. The ampersand joins them into a criteria expression Excel can evaluate. For example, if D2 contains 1-Jan-2026, the first condition means “on or after 1-Jan-2026.” The date display depends on your regional settings, but Excel stores valid dates as serial numbers.

Worked example with an inclusive end date

Suppose A2:A6 contains 1-Jan-2026, 15-Jan-2026, 31-Jan-2026, 31-Jan-2026 14:30, and 1-Feb-2026. Suppose B2:B6 contains 100, 50, 200, 25, and 80, respectively. Put 1-Jan-2026 in D2 and 31-Jan-2026 in E2. With that layout, use =SUMIFS(B2:B6,A2:A6,">="&D2,A2:A6,"<"&E2+1). The result is 375: 100 + 50 + 200 + 25. The February amount is excluded.

The 31-Jan entry with a time is included because it is earlier than midnight at the start of 1-Feb. A condition of “less than or equal to 31-Jan” can exclude that timestamp: the stored value for 31-Jan 14:30 is greater than the serial value for midnight on 31-Jan. That is the reason the next-day boundary is useful for transaction logs, exports, and other data that record times as well as dates.

If your source values truly contain dates only, and you want the formula to read more literally, you can use an inclusive upper bound instead. With the same layout of dates in A2:A6, amounts in B2:B6, and boundary dates in D2 and E2, the formula is =SUMIFS(B2:B6,A2:A6,">="&D2,A2:A6,"<="&E2). This returns the same 375 for the example’s date-only values, but it omits the 31-Jan 14:30 row. Choose the version that matches the data rather than assuming every displayed date is stored at midnight.

Using fixed dates or date cells

Keeping start and end dates in worksheet cells makes a report easier to reuse: change either date and the total updates. It also avoids typing dates inside a formula, where date formats and regional settings can be confusing. Enter dates into D2 and E2 using a format Excel recognizes, then apply a date number format if they do not display as expected.

For a fixed period, DATE can create the boundary values without relying on how a typed date string is interpreted. With dates in A2:A6, amounts in B2:B6, and a requirement to total January 2026 including any time of day, use =SUMIFS(B2:B6,A2:A6,">="&DATE(2026,1,1),A2:A6,"<"&DATE(2026,2,1)). Using the five-row example above, the result is 375. DATE takes year, month, and day as separate numbers, so this formula is not dependent on whether the computer normally displays dates as day/month/year or month/day/year.

When copying a formula to other cells, consider locking the data ranges and boundary cells with dollar signs. With dates in A2:A100, amounts in B2:B100, and start and end dates in D2 and E2, this copy-friendly version is =SUMIFS($B$2:$B$100,$A$2:$A$100,">="&$D$2,$A$2:$A$100,"<"&$E$2+1). The dollar signs keep the references from shifting if you fill the formula down or across. They do not change the calculation.

Summing a month or another changing period

For a monthly report, use the first day of the month as the lower boundary and the first day of the next month as the exclusive upper boundary. This avoids needing to calculate whether the month has 28, 29, 30, or 31 days. With dates in A2:A100, amounts in B2:B100, and any date in the target month in D2, use =SUMIFS(B2:B100,A2:A100,">="&DATE(YEAR(D2),MONTH(D2),1),A2:A100,"<"&DATE(YEAR(D2),MONTH(D2)+1,1)). If D2 is any date in January 2026 and the example rows are in rows 2 through 6, the result is 375.

DATE handles a month value that moves past December: for example, month 13 rolls into January of the following year. That makes the next-month boundary practical for monthly summaries. You can put one date from each desired month in a report column and copy a suitably anchored formula down, adjusting references as needed.

For a rolling period such as the previous 30 days, first decide what “previous 30 days” means in your report. It might include today and the 29 preceding calendar days, or it might mean a 30-day window ending at the current moment. Those definitions have different boundaries. For a transparent, auditable report, storing the chosen start and end dates in cells is often preferable to hiding the period definition inside a long formula.

Preparing the date and amount columns

A formula can be syntactically correct and still return zero if the dates in the criteria range are text rather than Excel date values. A date that only looks like a date is not necessarily stored as one. One quick check is to select a source cell and temporarily change its number format to General. A genuine date typically displays as a number; text generally remains text. Confirm with the source system or convert the data carefully before relying on a total.

Other checks that help keep date totals reliable include:

  • Make sure the dates and amounts are aligned by row, with each value in the sum range corresponding to the date in the same row.
  • Use equally sized criteria and sum ranges. For example, A2:A100 and B2:B100 cover the same 99 rows.
  • Check for unexpected blanks, imported text, or errors in the date column and amount column.
  • Confirm the start date is not later than the end date, and confirm both cells contain the intended year.
  • Check whether timestamps are present even if the cells are formatted to show only the date.

Changing a cell’s number format changes how its value appears; it does not convert date text into a real date. If data came from a CSV or another system, conversion may be needed before SUMIFS can compare it reliably. The right method depends on the import and the text format, so inspect a small sample and verify the resulting values rather than assuming formatting alone fixed the issue.

Common errors and troubleshooting

A zero result often points to a mismatch between the criteria and the stored data. Check that the start and end cells are real dates, that the source date column is numeric date data, and that the selected year is correct. Also check whether the date values include times: a simple end-date comparison can miss times later in the final day.

If Excel displays a formula error, check the function’s argument order and the size of each range. SUMIFS takes the sum range first, unlike SUMIF, which takes the criteria range first. In Microsoft 365 for Windows with standard settings, commas separate arguments. Some regional configurations use semicolons as the list separator; if Excel rejects an otherwise correct formula, use the separator configured for that installation.

Another frequent mistake is writing the operator and cell reference as a single quoted phrase. The criteria needs to join the text operator to the cell value, as in ">="&D2, rather than treating D2 as literal text. Also avoid comparing formatted date labels or using a text string that Excel may interpret differently under another locale.

For a quick check, filter the source table to the intended period and compare the visible amount total with the formula result. A filter is a useful diagnostic, but it does not repair invalid date data or prove that every row is correctly categorized. If the values disagree, inspect the first and last dates in the filtered records and look for times, text dates, duplicate records, or rows outside the intended sum range.

SUMIF alternatives and when to use them

SUMIF remains useful when there is only one condition. With dates in A2:A6 and amounts in B2:B6, the formula =SUMIF(A2:A6,">="&D2,B2:B6) totals amounts dated on or after the start date in D2, without an end-date limit. Similarly, a single upper boundary can be applied with a less-than or less-than-or-equal-to criterion. For a bounded period, however, SUMIFS communicates the two requirements directly.

It is possible to subtract a sum before the period from a sum through the end date, but that approach involves two calculations and is less straightforward to maintain. SUMIFS also makes it easier for another person to see the intended range. If your date report must also filter by a category, salesperson, or region, SUMIFS can add those conditions too; add each extra criteria range and its matching criterion as a pair.

For a cleaner, growing data source, consider formatting the data as an Excel table with Insert > Table. Table references can expand as rows are added, which helps avoid formulas that stop at an old last row. The exact structured-reference formula depends on the table and column names, so use the names shown in your workbook and check the result after adding a new record.

Frequently Asked Questions

Can SUMIF add values between two dates?
Not with one SUMIF condition alone: it accepts one criterion. Use SUMIFS with a lower and upper date condition for a clear date-range total. SUMIF can handle a single boundary, such as dates on or after a start date.

Should I use less than or equal to the end date?
Use less than or equal to the end date when the source contains date-only values. If timestamps might be present, use less than the day after the end date so times throughout the final day are included.

Why does my date-range formula return zero?
Check whether the source dates and boundary cells are real Excel dates rather than text, whether the year and range are correct, and whether the ranges cover the same rows. A time component or an incorrectly imported date can also prevent a row from meeting the criteria.

How do I sum a whole month?
Use the first day of the target month as the inclusive start and the first day of the next month as the exclusive end. That pattern handles different month lengths and includes timestamps on the month’s final day.

Can I use a date typed directly in the formula?
It is usually safer to use a cell reference or DATE(year,month,day). A typed date string can be interpreted differently depending on regional date settings, whereas DATE makes the year, month, and day explicit.

Related

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.