SUMIF a Date Between Two Dates in Excel

Formulas & functions · Updated November 20, 2026

If your dates are in A2:A100, the values to add are in B2:B100, and the start and end dates are in E2 and F2, use =SUMIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2). This adds values whose dates fall on or after the start date and on or before the end date; despite the common search term “SUMIF,” the two-condition function you need is SUMIFS.

The SUMIFS formula for a date range

SUMIFS adds numbers only when every condition you specify is met. For a date interval, the date column is checked twice: once against the lower boundary and once against the upper boundary. The formula above uses a greater-than-or-equal condition for E2 and a less-than-or-equal condition for F2, so both boundary dates count.

The formula assumes that A2:A100 contains the dates, B2:B100 contains the amounts, E2 contains the beginning date, and F2 contains the ending date. The ranges should cover the same rows and have the same dimensions. If your data extends farther down, adjust both data ranges together, for example to row 500.

The ampersand joins each comparison operator to the date in its cell. Excel therefore evaluates the criteria as “on or after the value in E2” and “on or before the value in F2.” Keeping the dates in cells rather than typing them into the formula makes it easier to reuse the calculation for different periods.

Why SUMIF and SUMIFS are different

SUMIF handles one condition, such as adding all amounts for one department or one date. A range with two boundaries needs two conditions, which is why SUMIFS is the natural choice. The names are easy to mix up: SUMIF has no final S, while SUMIFS does.

There is also a small but important argument-order difference. SUMIF places its criteria range before the sum range. SUMIFS starts with the sum range, followed by pairs of criteria ranges and criteria. In the example, B2:B100 is the sum range, then A2:A100 and the start-date criterion form one pair, and A2:A100 and the end-date criterion form the second pair.

For this task, you do not need to subtract one SUMIF result from another or build a complicated array expression. SUMIFS is designed to combine conditions, is easier to read later, and can accept additional criteria if you need to narrow the total further.

Prepare the date and amount columns

Excel must recognize the entries in the date column and the two boundary cells as dates, not merely as text that looks like a date. Excel stores a real date as a serial value and displays it using a date format. You can usually change the appearance without changing the underlying value.

  • Put one date or date-and-time value in each row of the date column, and put the corresponding number to add in the same row of the amount column.
  • Enter a genuine start date in E2 and end date in F2. Avoid extra spaces or a leading apostrophe, which can leave an entry stored as text.
  • If you need to check the display, select the date cells and choose Home > Number Format > Short Date. A different display format does not change which rows qualify.
  • Make sure the start date is not later than the end date. If it is, no ordinary date can satisfy both conditions, so the result will generally be zero.

How Excel interprets typed dates can depend on regional settings. For example, a numeric date with the day and month reversed may be understood differently in another locale. Using clearly entered dates, or selecting dates from a calendar control where available, helps avoid that ambiguity. The formula itself works in Microsoft 365 for Windows, Excel for Mac, and Excel for the web; regional list-separator settings may display semicolons instead of commas in some installations.

Worked example with inclusive dates

Suppose A2:A5 contains March 1, March 15, March 31, and April 1, 2025, while B2:B5 contains 120, 80, 225, and 60. Put March 1, 2025 in E2 and March 31, 2025 in F2. With those cells and values, the following formula returns 425:

For this example, dates are in A2:A5, amounts are in B2:B5, and the inclusive start and end dates are in E2 and F2: =SUMIFS(B2:B5,A2:A5,">="&E2,A2:A5,"<="&F2).

The arithmetic is 120 + 80 + 225 = 425. March 1 and March 31 both count because the criteria use inclusive comparisons. April 1 is after the end date, so its amount of 60 is excluded. If the start date were March 15 instead, the same formula would include March 15 and March 31, returning 305.

This is useful for monthly totals, date-bounded invoices, event counts, and sales summaries. The cells holding the date limits can be changed whenever the reporting period changes; the calculation updates without editing the formula.

Handling dates that include times

A date-and-time value contains both a date and a time, even if the cell is formatted to show only the date. If the final date cell F2 represents midnight at the start of that day, a criterion of less than or equal to F2 will include midnight but exclude later times on that same date. This can make a total appear to omit records from the final day.

When the end-date cell contains a date with no meaningful time component, use a less-than comparison against the day after it. That includes every time on the selected end date while excluding midnight at the start of the following date.

For dates in A2:A100, amounts in B2:B100, a start date in E2, and a date-only end date in F2, use this version to include all times on the end date: =SUMIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<"&(F2+1)).

For instance, if F2 is March 31, this upper limit includes a record on March 31 at 11:45 p.m., but excludes a record at midnight on April 1. This approach assumes F2 is a date representing the whole day. If F2 intentionally contains a specific time and you want to stop at that precise instant, use a boundary that reflects that requirement instead of adding one day.

Adding another condition or changing the boundaries

You can add criteria to SUMIFS without changing the basic date-range logic. For example, a report might total a date range for only one region, product, or account. Each added condition needs a criteria range and a criterion, and the ranges should line up row by row with the date and sum ranges.

For dates in A2:A100, amounts in B2:B100, regions in C2:C100, a start date in E2, an end date in F2, and the region to match in G2, use: =SUMIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2,C2:C100,G2). This adds an amount only when its date is within the inclusive interval and its region matches G2.

To make the beginning date exclusive, change the first comparison from greater than or equal to greater than. To make the end date exclusive, change the second comparison from less than or equal to less than. Be deliberate about these choices: “between” often means inclusive for calendar reporting, but a business rule or time interval may define the boundaries differently.

Common problems and ways to diagnose them

If the result is zero or unexpectedly small, check the underlying values before changing the formula. Formatting can hide what Excel actually stores, and a date that looks correct on screen may still be text or may include an unseen time.

  • Date entries stored as text: Text dates may not compare as expected with genuine Excel dates. Re-enter or convert them into actual dates, then check that changing the number format changes their display.
  • Times on the final date: If the date column includes times, use the next-day exclusive upper boundary described above when the whole end date should count.
  • Different range sizes: The date criteria range and sum range should cover corresponding rows. Using dates from rows 2 through 100 but amounts from rows 2 through 99 can produce an error or incorrect results.
  • Wrong start or end cell: Confirm that E2 is the lower date and F2 is the upper date, and that the formula points to those cells rather than to nearby labels.
  • Amounts stored as text: SUMIFS adds numeric values. If amounts were imported as text, convert them to numbers before relying on the total.
  • Criteria typed without joining the cell: When the operator is combined with a cell reference, the ampersand is necessary. A criterion such as ">="&E2 is different from typing an operator and a cell address as one unquoted expression.

If the formula displays as text in the cell instead of calculating, check whether the cell is formatted as Text and whether the formula begins with an equals sign. Change the cell to a suitable number format and re-enter the formula. Also check that calculation is enabled if other formulas in the workbook are not updating.

When a different approach makes sense

For one total with date boundaries, SUMIFS is usually the simplest and clearest option. It works well in a regular range and in an Excel table, provided the criteria ranges and sum range refer to corresponding records. If the data is an Excel table, structured references can make formulas easier to read as columns are added, though the exact reference names depend on the table and its headers.

A PivotTable may be more useful when you need totals for many months, regions, or categories at once, rather than a single date interval. You can summarize a date field and inspect totals by period, but the result is a report layout rather than one formula-driven total. A filter can also help you inspect matching records, although filtering alone does not automatically provide the same reusable calculation.

For a recurring report, keep the start and end dates in clearly labeled input cells and use the same SUMIFS formula. That separation makes the rule visible and reduces the chance that someone will overlook a hard-coded date inside a formula. If records contain timestamps, decide explicitly whether each period includes the entire final calendar day or ends at a particular time.

Frequently Asked Questions

Can SUMIF check whether a date is between two dates?
SUMIF is built around one criterion, while a date interval needs a lower and an upper condition. Use SUMIFS with the date range supplied twice, once for each boundary.

Are the start and end dates included?
Yes, the formula using greater than or equal to the start and less than or equal to the end includes both dates. Replace either operator if that boundary should be excluded.

Why does my formula miss records on the end date?
If those cells contain times, a less-than-or-equal comparison to a date-only end cell usually stops at midnight. Compare dates to the day after the end date using a strict less-than condition to include all times on the final day.

Can I type the dates directly into the formula?
You can, but date literals can be harder to read and may be interpreted differently across regional settings. Start and end date cells are easier to update and make the formula more reusable.

Does this formula work in Excel for Mac or Excel for the web?
SUMIFS is available in Microsoft 365 for Windows, Mac, and the web, and the same logic applies. Depending on regional settings, Excel may use semicolons rather than commas as argument separators.

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.