Sumifs with date range?

To sum values between two dates in Excel, use SUMIFS with the same date column as both a start-date and end-date criterion. With transaction dates in A2:A5, amounts in C2:C5, and the start and end dates in E2 and E3, respectively, the formula is =SUMIFS(C2:C5,A2:A5,">="&E2,A2:A5,"<="&E3). If the four amounts are 100, 75, 25, and 200 on January 5, January 20, January 31, and February 1, 2026, and E2 and E3 contain January 1 and January 31, the result is 200.

How SUMIFS handles a date range

SUMIFS adds numbers only when every condition you specify is met. Its first argument is the range to add; the remaining arguments come in pairs: a criteria range followed by a criterion. For a date interval, the date column appears twice. The first criterion sets the lower boundary, and the second sets the upper boundary.

The example includes both endpoints: dates must be greater than or equal to the start date and less than or equal to the end date. In Excel, dates are stored as serial numbers, so comparisons work when the cells contain genuine Excel dates. The visible formatting—such as January 5, 2026, or 1/5/2026—does not change the underlying value.

Using cells for the boundary dates is generally more reliable than typing date text into the formula. It also makes the calculation easy to reuse: change E2 or E3, and Excel recalculates the sum for the new period.

Build the formula from your worksheet layout

Before writing the formula, identify three things: the numbers to add, the column containing the dates, and the cells that hold the interval boundaries. The sum range and each criteria range should cover corresponding rows. If the dates begin in row 2 and end in row 100, the amount range should normally cover those same rows.

Sum range: the values you want to total, such as invoice amounts.
Date criteria range: the cells containing transaction, invoice, or delivery dates.
Start and end cells: two cells containing actual dates, not date-looking text.

With dates in A2:A100, amounts in C2:C100, the start date in E2, and the end date in E3, the formula is =SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&E3). For example, if the matching amounts in the date interval are 45, 80, and 25, the result is 150. The formula does not require the records to be sorted by date.

Use criteria cells rather than hard-coded dates

Each date criterion combines a comparison operator with a date value. The ampersand joins the operator, such as >=, to the value in a cell. The resulting criterion tells Excel to include dates on or after the start. The upper criterion works the same way with <=.

With dates in A2:A6, amounts in C2:C6, E2 set to January 1, 2026, and E3 set to January 31, 2026, the formula is =SUMIFS(C2:C6,A2:A6,">="&E2,A2:A6,"<="&E3). If the five amounts are 10, 20, 30, 40, and 50, and their dates are January 1, January 8, January 15, January 31, and February 1, the result is 100: the first four amounts qualify, while the last does not.

A common misconception is that Excel needs the dates written in a particular display format for the comparison to work. It does not; it needs valid date values. Formatting controls how a date appears, while the stored value controls the comparison.

Choose inclusive or exclusive boundaries

The usual date-range formula includes both the first and last dates. That is often what a reader expects when asking for sales “from January 1 through January 31.” However, a date cell can also contain a time, even when its number format displays only the date. A value such as January 31 at 3:00 p.m. is later than January 31 at midnight. As a result, an inclusive upper criterion of less than or equal to the end-date cell can omit records later in that final day.

For a full-day interval that may include timestamps, use the day after the end date as an exclusive upper boundary. With timestamps in A2:A5, amounts in C2:C5, and the first and last calendar dates in E2 and E3, use =SUMIFS(C2:C5,A2:A5,">="&E2,A2:A5,"<"&E3+1). If E2 is January 1 and E3 is January 31, this includes values from January 1 at midnight up to—but not including—February 1 at midnight. A January 31 record at 3:00 p.m. is included. In the four-row example above, the result is 200.

This exclusive-boundary pattern is useful for timestamps because it avoids guessing the last possible time of day. For date-only data, the ordinary inclusive formula is straightforward and appropriate. Choose the version that matches whether your source data contains times.

Sum all dates in a month

To total a month, use the first day of that month as the inclusive lower limit and the first day of the next month as the exclusive upper limit. This avoids manually calculating whether the month has 28, 29, 30, or 31 days. Put any date in the month you want to summarize in E2.

With dates in A2:A5, amounts in C2:C5, and E2 containing any date in January 2026, the formula is =SUMIFS(C2:C5,A2:A5,">="&DATE(YEAR(E2),MONTH(E2),1),A2:A5,"<"&DATE(YEAR(E2),MONTH(E2)+1,1)). For the sample amounts dated January 5, January 20, January 31, and February 1, the result is 200. The formula builds January 1 as the start and February 1 as the cutoff, so the February record is excluded.

This is also a sound choice when the date column contains times. If you need a monthly report for many months, place one representative date from each month in a summary column and copy the formula down; each row will use its own month. Check that the summary date cells are real dates too.

Add other conditions to the date range

SUMIFS can apply a date interval and other conditions at the same time. For instance, a sales report might total one product category only during a selected month. Add another criteria-range and criterion pair after the date pairs. All conditions must be true for a row to contribute to the sum.

With dates in A2:A5, categories in B2:B5, amounts in C2:C5, start and end dates in E2 and E3, and the category to match in G2, use =SUMIFS(C2:C5,A2:A5,">="&E2,A2:A5,"<="&E3,B2:B5,G2). If the two January rows labeled Hardware have amounts of 100 and 25, and G2 contains Hardware, the result is 125. January rows in other categories and all February rows are ignored.

For text conditions, the criterion can be a cell reference, as above, or a text value in quotation marks. Wildcards can help when names are inconsistent: an asterisk represents any number of characters, and a question mark represents one character. Keep the date conditions separate from text conditions so the formula remains readable, and ensure all ranges have matching dimensions.

Common problems and how to fix them

A result of zero or an unexpectedly small total often means Excel is not comparing the values you think it is. Check the following before changing the formula:

Dates stored as text: A value that looks like a date may be text imported from another system. Try changing its number format; if it does not behave as a date, convert the source values to real Excel dates before relying on date comparisons.
Boundary cells include a time: A start date set to noon excludes earlier times that day. For a whole calendar day, use a date-only boundary at midnight; for the end, use the exclusive next-day approach when timestamps exist.
Ranges do not line up: If the sum range has a different number of rows from the date range, the result can be wrong or Excel may return an error. Make all criteria ranges align with the sum range.
Dates are reversed: If the start date is later than the end date, no ordinary date can meet both conditions. Verify the two input cells and their labels.
Wrong year or regional interpretation: A typed date such as 3/4/2026 can be interpreted differently depending on regional settings. Enter dates using unambiguous date entry or use cells whose values you can verify.

It can help to temporarily format the boundary cells as dates and inspect a few source rows, including the first and last dates. Also check that there are no accidental blanks or formulas returning text in the date column. The range formula cannot reliably include text values that merely resemble dates.

When to use another approach

For a standard total by date interval, SUMIFS is usually the clearest choice: it expresses the sum range and each condition directly and is available in current desktop Excel. Use a PivotTable instead when you need to explore totals by month, category, or other fields interactively, or when the report should be rearranged without editing formulas.

A helper column can make a complicated workbook easier to audit. For example, it can label each row with a reporting period or a cleaned date when imported data is inconsistent. Fixing the source dates is preferable when possible; a formula cannot make text dates dependable merely by comparing them with valid date cells.

For Microsoft 365 and supported newer Excel versions, functions such as FILTER can return matching records rather than just adding their amounts. That is useful when you want to inspect the underlying rows, but it is a different task from calculating a total. If your goal is only a conditional sum, adding a more complex formula may make the workbook harder to maintain without improving the result.

Frequently Asked Questions

Can SUMIFS use a start date and an end date?
Yes. Use the date range as two criteria ranges: one with a greater-than-or-equal start criterion and one with a less-than-or-equal end criterion. Use an exclusive next-day cutoff instead when the end date may contain timestamps.

Does SUMIFS include the first and last dates?
It does when the criteria use >= for the start and <= for the end. If you use a less-than cutoff at the day after the end date, that cutoff itself is excluded, while every time on the end date is included.

Can I sum by month without typing the month’s last day?
Yes. Set the lower boundary to the first day of the selected month and the upper boundary to the first day of the next month, excluding that upper boundary. This works for months of different lengths and handles timestamps cleanly.

Why does my date-range formula return zero?
Check that the source dates are real Excel dates rather than text, the date cells contain the intended year, and the start date is not later than the end date. Also confirm the criteria ranges and sum range cover corresponding rows.

Can SUMIFS match dates and a category at once?
Yes. Add a category criteria range and its matching criterion as another pair of arguments. A row contributes only when it meets the date conditions and the category condition.

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.