How to Sum Values Between Two Dates in Excel

To sum values between two dates in Excel, use SUMIFS with criteria that set the start and end of the date range.

The key detail is whether both boundary dates should count, because that affects the formula and the result.

Use SUMIFS to sum between two dates

Suppose transaction dates are in cells A2:A100, and the amounts to total are in B2:B100.

Put the start date in D2 and the end date in E2.

To include both dates, use:

=SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2)

SUMIFS adds values from its first argument, the sum range, when every listed condition is met.

Here, each date must be greater than or equal to the start date and less than or equal to the end date.

The formula therefore includes transactions posted on either boundary date.

For example, if the start date is January 1, 2026, and the end date is January 31, 2026, the formula totals qualifying amounts from January 1 through January 31.

It excludes December 31 and February 1.

Enter the dates directly in the formula

You can also specify dates in the formula instead of referencing cells.

The DATE function is a reliable way to create date values without depending on the computer’s regional date format:

=SUMIFS(B2:B100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<="&DATE(2026,1,31))

The arguments to DATE are year, month, and day.

This example sums amounts from January 1 through January 31, 2026, inclusive.

For a formula you plan to reuse, cell references are usually easier to update than fixed dates.

Include all times on the end date

A date-and-time value contains both a date and a time.

Excel stores these values as numbers: the whole-number portion represents the date, and the fractional portion represents the time.

If the end date in E2 means midnight at the start of that day, a criterion of <=E2 can omit later times on that date.

To include every time on the end date, use an exclusive next-day boundary:

=SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&(E2+1))

This includes timestamps from the start date through the end date, up to but not including midnight on the following day.

It is a useful pattern for daily logs, sales records, and exported data that includes timestamps.

For date-only values, the inclusive <=E2 version is also appropriate.

Sum a date range for one category

SUMIFS can apply additional conditions.

If categories are in C2:C100 and the category to match is in F2, add that range and criterion after the date criteria:

=SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&(E2+1),C2:C100,F2)

This sums only amounts whose dates fall in the selected period and whose category matches F2.

For instance, selecting “Travel” in F2 returns the period’s travel total, assuming the category values match consistently.

Excel and Google Sheets compatibility

SUMIFS is available in current desktop and web versions of Excel, including Excel 2019, Excel 2021, and Microsoft 365.

It is also available in Google Sheets.

The examples use commas to separate arguments; some regional settings use semicolons instead.

If Excel reports a formula syntax error, check the list separator configured for your locale.

In older Excel versions, SUMIFS may not be available.

A legacy alternative is SUMPRODUCT:

=SUMPRODUCT((A2:A100>=D2)*(A2:A100<(E2+1))*B2:B100)

This applies the same date boundaries and multiplies the matching rows by their amounts before summing.

Keep the ranges the same size; mismatched range dimensions can cause errors or unexpected results.

Common problems and how to check them

• Dates stored as text: A cell that looks like a date may contain text, which date comparisons may not treat as a numeric date.
  
  Try changing the cell format or converting the source values to real dates.
  
  Formatting alone does not convert text into dates.
• Start date is after end date: The criteria cannot match a normal date range in this case.
  
  Check the values in D2 and E2, or add a separate validation message for reversed inputs.
• Ranges do not align: The sum range and each criteria range should cover the same rows.
  
  For example, pair B2:B100 with A2:A100, rather than starting one range on a different row.
• Unexpected zero: Confirm that the date cells contain actual dates, the amount cells contain numbers, and the criteria reference the intended start and end cells.
  
  Also check for extra spaces or inconsistent category labels if you added a category condition.
• Header rows included: Start the ranges on the first data row.
  
  A header such as “Amount” should not be included in the sum range.

To check the date values, select a date cell and inspect its value in the formula bar.

You can also temporarily change the number format to General; a real Excel date displays as a serial number.

Restore the date format afterward if needed.