Excel SUMIFS Between Two Dates: Formula and Examples

Formulas & functions · Updated November 19, 2026

To sum values in Excel between two dates, use SUMIFS with the date range as a criteria range and two criteria: one greater than or equal to the start date, and one less than or equal to the end date. With dates in A2:A100, amounts in C2:C100, and the start and end dates in E2 and F2, use =SUMIFS($C$2:$C$100,$A$2:$A$100,">="&E2,$A$2:$A$100,"<="&F2).

The basic SUMIFS formula

SUMIFS adds values only when every supplied condition is met. Its first argument is the range to add; after that, provide a criteria-range and criterion pair for each condition. To set a date interval, use the same date column twice: once to test the lower boundary and once to test the upper boundary. The ampersand joins each comparison operator to the date in a cell.

For the formula above, Excel adds a value in C only when the corresponding date in A is on or after E2 and on or before F2. Both boundaries are included. This is usually what people mean by “between two dates,” such as summing all sales from June 1 through June 30.

In Microsoft 365 for Windows, enter the formula in the result cell and press Enter. The same SUMIFS formula is available in Excel for Mac and Excel for the web. Some regional settings use semicolons rather than commas to separate formula arguments; if Excel rejects a formula using commas, check your system’s list-separator setting.

Set up the dates and values

Keep each transaction’s date and amount on the same row, and use real Excel dates rather than date-looking text. For example, a compact table might have transaction dates in A2:A6 and amounts in C2:C6:

  • May 31: 40
  • June 1: 120
  • June 15: 80
  • June 30: 200
  • July 1: 50

Put June 1 in E2 and June 30 in F2, then use the basic formula with A2:A6 as the date range and C2:C6 as the sum range. It returns 400: 120 + 80 + 200. The May and July amounts are excluded. Format E2 and F2 as dates if you want them to display clearly; cell formatting changes how a date appears, not the underlying date value.

The ranges in a SUMIFS formula should have matching dimensions. For instance, if the sum range is C2:C100, each criteria range should also cover rows 2 through 100. Avoid using C:C for the sum range and A2:A100 for the date range: those ranges do not match in size and can produce an error.

Why the criteria are written this way

The criterion ">="&E2 combines the text operator “greater than or equal to” with the date value in E2. The second criterion does the same with “less than or equal to” and F2. The comparison operators must be enclosed in quotation marks, and the ampersand joins the operator to the cell reference. Typing ">=E2" instead would make Excel look for text containing the characters E2, rather than use the date stored in that cell.

Order matters in the argument list: SUMIFS starts with the sum range, followed by criteria-range and criterion pairs. This differs from SUMIF, which puts its criteria range first. When adapting a formula, check that you have not carried over the argument order from SUMIF.

If the start and end dates are typed directly into a formula, use Excel’s DATE function instead of relying on a date string that might be interpreted differently under another regional date format. With dates in A2:A100 and amounts in C2:C100, this formula totals all transactions in June 2026 and returns 400 for the sample data above:

=SUMIFS($C$2:$C$100,$A$2:$A$100,">="&DATE(2026,6,1),$A$2:$A$100,"<="&DATE(2026,6,30))

Choose inclusive or exclusive boundaries

The standard formula includes both the start date and the end date. That is appropriate for a report labelled “June 1 to June 30,” because transactions on either date count. For a period that should exclude the start date, change the first operator from >= to >. To exclude the end date, change the second operator from <= to <.

Another useful convention is a half-open interval: include the start date but exclude the next period’s start date. For dates in A2:A100, amounts in C2:C100, and the first day of the desired month in E2, this formula adds dates on or after E2 but before the first day of the following month. For June 2026 and the sample data, it returns 400:

=SUMIFS($C$2:$C$100,$A$2:$A$100,">="&E2,$A$2:$A$100,"<"&DATE(2026,7,1))

This approach is convenient for monthly reporting because it avoids calculating the month’s final calendar day. It also becomes particularly important when the date column contains times.

When your date cells also contain times

Excel stores a date-time as a date serial number with the time represented as a fraction of a day. A cell displaying June 30 may therefore contain June 30 at 2:30 p.m. If your end criterion is less than or equal to midnight at the start of June 30, that afternoon transaction is later than the boundary and will be missed.

For date-time values, use an exclusive upper limit: before the day after the end date. With date-time entries in A2:A100, amounts in C2:C100, and the final calendar date in F2, this formula includes every time on the end date. If June 30 contains transactions totalling 200, that amount is included:

=SUMIFS($C$2:$C$100,$A$2:$A$100,">="&E2,$A$2:$A$100,"<"&F2+1)

Here E2 is the start date and F2 is the final date to include. The test is earlier than midnight on the following day, rather than no later than midnight at the start of F2. This avoids having to guess the final possible time value, such as 11:59:59 p.m.

Add another condition, such as a category

You can combine a date interval with other filters by adding more criteria-range and criterion pairs. For example, suppose dates are in A2:A100, amounts are in C2:C100, and category labels are in D2:D100. Put the category to match in G2. With the same date boundaries in E2 and F2, this formula sums only rows in the date interval whose category matches G2:

=SUMIFS($C$2:$C$100,$A$2:$A$100,">="&E2,$A$2:$A$100,"<="&F2,$D$2:$D$100,G2)

For example, if G2 contains “Online,” only qualifying Online amounts are included. Each added condition narrows the rows that can contribute to the total; a row must satisfy the date tests and the category test. This is useful for sales by month and channel, hours by period and employee, or expenses by period and account.

Common problems and how to fix them

  • The result is zero even though matching rows exist: Check whether the dates are true Excel date values or text. A date imported from a file may look correct but not compare as a date. Try changing the cell format, and if that does not change the display or behavior, convert the text values to dates before summing.
  • The final day is missing: If the date column contains times, replace the inclusive end-date test with a less-than test against the day after the end date, as shown above.
  • Excel shows a formula error: Confirm the comma or semicolon argument separator for your locale, check that the quotation marks are straight quotes, and verify that each function argument is separated correctly.
  • The answer is too large or too small: Make sure the sum range and date criteria ranges start and end on the same rows. Also check that the start date is not later than the end date and that the selected amount column contains the intended values.
  • A date typed into the formula behaves differently on another computer: Use date cells or DATE(year,month,day) rather than an ambiguous text date. Formats such as 04/05/2026 can mean different dates depending on regional settings.

For a quick diagnostic, test one row you expect to be included: inspect its underlying date value, the corresponding amount, and whether it satisfies both boundaries. Also confirm that no extra spaces or inconsistent category labels are affecting any additional criteria.

SUMIFS compared with other ways to total dates

For a simple total over a date interval, SUMIFS is usually the clearest option: the range and both boundaries are visible in one formula, and extra filters can be added as criteria pairs. It is available in modern Excel and older Excel versions, so a SUMIFS-based workbook is generally a practical choice when it may be opened by people using different editions.

A PivotTable is an alternative when you need to explore totals by month, quarter, category, or other groups without writing a separate formula for each result. 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 may be grouped into periods through the PivotTable interface. A PivotTable is useful for interactive summaries; a formula is often easier when a report needs a fixed total in a particular cell.

For a one-off total, manually filtering dates and adding visible values is less reliable: filters can change, and a normal SUM may include hidden rows. SUMIFS calculates against its stated conditions, independent of whether rows are currently hidden by a filter. If you need a total that responds specifically to visible rows, that is a different requirement and may call for a subtotal-based design rather than this standard date-range formula.

Frequently Asked Questions

Does SUMIFS include the start and end dates?
Yes, when the criteria use >= for the start and <= for the end. If the date column contains times and you want to include all times on the end date, use an exclusive upper boundary at the next day instead.

Can I use SUMIFS between two dates without helper cells?
Yes. You can put the dates in the formula with DATE(year,month,day), or use references to cells containing dates. Cell references are usually easier to maintain because you can change the reporting period without editing the formula.

Why does SUMIFS return zero for dates that look correct?
The date entries may be text rather than Excel date values, or the formula’s boundaries may not match the date-time values in the column. Check the underlying entries and use a less-than-next-day upper limit if times are present.

Can SUMIFS use dates and a category at the same time?
Yes. Add the category range and its criterion after the two date criteria. SUMIFS includes only rows that meet all the listed conditions.

What is the safest way to sum a full month?
Use a start date at the beginning of the month and an exclusive limit at the beginning of the next month. This includes the entire final day even when entries have times and avoids manually working out the month’s last date.

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.