Excel SUMIF Between Dates: Use SUMIFS Correctly

Formulas & functions · Updated November 19, 2026

To sum values in Excel for dates between a start and end date, use SUMIFS with two conditions on the date range: one for dates on or after the start, and one for dates on or before the end. For example, with dates in A2:A100, amounts in C2:C100, and the boundary dates in E2 and F2, use =SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2).

Why SUMIFS Is the Right Choice

Although people often search for “SUMIF between dates,” a date interval needs two tests: the date must be greater than or equal to the first boundary and less than or equal to the second. The single-condition SUMIF function handles only one criterion. SUMIFS is designed to sum values that meet multiple criteria, so it can apply both date tests to the same date column.

In the example formula, the first argument is the range to add. The next argument is the date range to check, followed by the first criterion. The date range and a second criterion follow for the end boundary. The ampersand joins each comparison operator, such as >=, to a date value in a cell. This creates a criterion Excel can evaluate against each date.

Make sure the sum range and criteria ranges cover corresponding rows. If amounts are in C2:C100, the date criteria ranges should normally also be A2:A100—not A1:A100 or A2:A101. A mismatch can cause errors or produce results that do not correspond to the intended records.

Set Up the Date Range and Sum Range

Keep each transaction on one row, with a real Excel date in one column and the value to add in another. Put the beginning and ending dates in separate cells so you can change the reporting period without editing the formula. For example, enter the start date in E2 and the end date in F2. Excel may display dates in a format such as 1/1/2025 or January 1, 2025; the display format does not change the underlying date value.

With transaction dates in A2:A5, amounts in C2:C5, and the start and end dates in E2 and F2, use:

=SUMIFS(C2:C5,A2:A5,">="&E2,A2:A5,"<="&F2)

Suppose the rows contain January 5, 2025, and 120; January 20 and 80; February 1 and 150; and January 31 and 200. If E2 is January 1 and F2 is January 31, the formula includes the first, second, and fourth transactions. The total is 120 + 80 + 200 = 400. The February 1 transaction is outside the range.

Enter the formula in the cell where you want the total. You can format that result as currency or a number using Home > Number. If you copy the formula to another report area, consider whether the input cells should remain fixed; references such as E2 and F2 will shift when copied unless you make them absolute, for example by using dollar signs in the cell references.

Choose Inclusive or Exclusive Date Boundaries

The formula above includes both boundary dates. The greater-than-or-equal test includes transactions on the start date, and the less-than-or-equal test includes transactions on the end date. That is usually what someone means by “between January 1 and January 31,” but it is worth confirming the intended rule before using the total in a report.

Sometimes the desired interval excludes one or both endpoints. For example, to include dates after the start but before the end, change the comparisons to strict greater-than and less-than tests. For a reporting period that begins on a particular date but ends immediately before another date, a common and clear convention is start-inclusive, end-exclusive. This avoids ambiguity when consecutive reporting periods meet at a boundary.

You can also enter fixed dates directly with Excel’s DATE function, which avoids relying on how a typed date string is interpreted. With transaction dates in A2:A100 and amounts in C2:C100, this example includes January 1 through January 31, 2025:

=SUMIFS(C2:C100,A2:A100,">="&DATE(2025,1,1),A2:A100,"<"&DATE(2025,2,1))

Here the ending test is before February 1, rather than on or before January 31. For date-only records, the result covers the same calendar days. The exclusive next-day boundary also works better when the date column contains times, as long as the range is intended to include every time on January 31.

Handle Dates That Also Contain Times

Excel stores a date and time as a serial value: the whole-number portion represents the date, and a fractional portion represents the time. A timestamp displayed as January 31 may therefore include a time such as 2:30 p.m. If the end criterion is less than or equal to a date-only value for January 31, timestamps later that day can be greater than that boundary and be left out.

For timestamp data, make the end boundary exclusive by using the day after the final date. With timestamps in A2:A100, values in C2:C100, the first included date in E2, and the last included calendar date in F2, use:

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

This includes times from the start of E2 up to, but not including, midnight after F2. For example, if F2 is January 31, a January 31 timestamp at 11:59 p.m. is included, while a February 1 timestamp is not. This approach is also useful for date-only values and helps keep the end-of-period rule consistent.

Check Whether Excel Recognizes the Dates

A cell can look like a date while actually containing text. Imported CSV files, copied web data, and dates written in a different regional order are common sources. If the criteria do not match, check a few cells: changing the number format to a date may reveal whether the value behaves like a date, but formatting alone does not convert text into a real date.

If A2 contains date text that Excel recognizes under the computer’s regional settings, you can convert it in a helper column. With the original text date in A2 and the conversion formula entered in B2, use:

=DATEVALUE(A2)

Fill the formula down, then format the helper results as dates and use that converted column as the criteria range. DATEVALUE cannot reliably resolve every ambiguous text date. For example, a string such as 03/04/2025 could mean March 4 or April 3 depending on its source and regional conventions. Confirm the source format rather than guessing. For an imported file, Excel’s Data > Text to Columns workflow may help convert recognized dates, but inspect the preview and choose the appropriate date order when the import options provide one.

Also verify that the boundary cells contain real dates, not text that merely looks like a date. Entering dates with Excel’s date controls or using DATE(year,month,day) is generally safer than building criteria from ambiguous text strings. If dates are entered as text in a formula criterion, regional settings can affect how they are interpreted.

Use Changing Date Boundaries

For recurring reports, storing the start and end dates in worksheet cells makes the calculation easier to maintain. A monthly summary can use the same SUMIFS formula each month; update E2 and F2 rather than rewriting its criteria. You can also copy the formula down a summary table if each row has its own start and end date, while keeping the transaction ranges fixed with absolute references.

For example, a summary table might list a month’s first date in E2 and its last date in F2, with the total beside them. If the summary is copied down, its boundary references can move to E3 and F3, while the source data ranges remain anchored. This is especially useful when the transaction list grows: convert it to an Excel table so that added rows are included in structured references, or update the formula ranges when new records are appended.

For periods that vary in length, such as a quarter or a custom contract period, use the actual intended start and end dates as inputs rather than assuming every period has the same number of days. Clearly labeled boundary cells also make the calculation easier for another person to review.

Common Errors and Quick Checks

  • No or unexpectedly low total: Confirm that the transaction dates are numeric Excel dates and that the start date is not later than the end date.
  • Last-day timestamps are missing: If the source includes times, use an exclusive boundary for the day after the final date rather than an inclusive comparison to a date-only value.
  • Wrong column is being added: Check the first argument; it must be the numeric amount range, not the date range.
  • Ranges do not line up: Use the same first and last row for the sum range and each criteria range.
  • Unexpected matches around a date: Check whether one of the date columns contains text, hidden times, or a different date interpretation from the imported source.
  • Formula shows an error after copying: Check whether the boundary references should move with the copied formula and whether the transaction ranges should be fixed with dollar signs.

When a result is still surprising, temporarily filter the source table to the same date interval and compare the visible records with the total. This is a practical way to spot a record outside the expected range, a text date, or a value stored as text. Remember that SUMIFS ignores text in the sum range rather than adding it as a number, so imported amounts may need their own cleanup.

SUMIF, SUMIFS, and Other Options

Use SUMIF when there is one criterion, such as adding all values for a single category. Use SUMIFS when there are multiple conditions, including a lower and upper date boundary. It can also combine date limits with other filters, such as a department or status, by adding more criteria-range and criterion pairs.

For a simple date interval, SUMIFS is usually easier to read and maintain than a more elaborate array calculation. If the goal is to display matching records rather than add a numeric column, a filtering or extraction approach may fit better; the right choice depends on whether the required output is a total, a count, or a list. For a count of records matching date conditions, Excel also has COUNTIFS, which uses multiple criteria but does not sum amounts.

Frequently Asked Questions

Can SUMIF check whether a date is between two dates?
Not with two independent criteria in one SUMIF calculation. Use SUMIFS to apply both the start-date and end-date conditions to the same date range.

Does “between dates” include the start and end dates?
It depends on the comparisons you use. Greater-than-or-equal and less-than-or-equal include both boundary dates; strict comparisons exclude them. For timestamps, an exclusive boundary at the start of the following day is often the clearest way to include the entire final day.

Why does my formula miss transactions on the last date?
The date cells may contain times. A timestamp on the final date is later than a date-only value representing midnight at the start of that day. Use the next day as an exclusive upper boundary, or remove the times if they are not needed.

Can I use dates typed directly in the criteria?
It is safer to refer to cells containing real dates or construct dates with DATE. Typed date text can be interpreted differently under different regional settings, and criteria need to match the actual values in the date column.

Can I add another condition, such as a department?
Yes. SUMIFS accepts additional criteria-range and criterion pairs. Add a range containing the department for each transaction and a cell or value identifying the department to include, keeping all ranges aligned row by row.

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.