To sum values between two dates in Excel, use SUMIFS with a start-date criterion and an end-date criterion. For dates in A2:A100, values in B2:B100, and boundary dates in D2 and E2, enter =SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2) to include both boundary dates; the function returns the total for rows whose dates fall in that range.
Why SUMIFS is usually the right choice
Although people often search for “SUMIF between dates,” Excel’s SUMIF function accepts only one condition. A date range needs two: the date must be on or after the start and on or before the end. SUMIFS is designed to apply multiple conditions, so it is the clearest option for this job. It is available in current Excel versions, including Microsoft 365.
The two date tests apply to the same date column. In the example above, the first criterion excludes dates before D2, and the second excludes dates after E2. Excel adds a value only when its row satisfies both conditions. The boundaries are inclusive because the operators are >= and <=. Change them to strict greater-than or less-than operators if the boundary dates should not count.
In SUMIFS, the sum range comes first, followed by pairs of criteria ranges and criteria. Each criteria range must have the same dimensions as the sum range. This order differs from SUMIF, where the criteria range comes first, so check the argument order when adapting an existing formula.
Set up the date and amount columns
A simple worksheet layout makes the formula easier to read and maintain:
- Column A contains transaction dates, beginning in A2.
- Column B contains the amounts to add, beginning in B2.
- D2 contains the requested start date, and E2 contains the requested end date.
Keep the date and amount rows aligned: A2 should describe the same transaction as B2. For example, if rows 2 through 100 contain the records, use A2:A100 as the date range and B2:B100 as the sum range. Avoid including a heading in only one range, or using ranges of different lengths; either can cause errors or misleading results.
Enter the boundaries as actual Excel dates, not text that merely looks like a date. To check or change a cell’s display format, select it and use Home > Number Format. You can also open Format Cells with Ctrl+1 in Windows; on Mac, use Command+1. Formatting changes how a date appears, but does not convert text into a real date value. If Excel treats a date as text, convert the cell contents before relying on date comparisons.
Worked example with inclusive dates
Suppose A2:A6 contains Jan 1, Jan 3, Jan 5, Jan 5, and Jan 8, while B2:B6 contains 120, 80, 150, 50, and 200. Put Jan 3 in D2 and Jan 5 in E2. The first and last transactions are outside the requested range; the three transactions dated Jan 3 or Jan 5 qualify. With dates in A2:A6, amounts in B2:B6, and the boundaries in D2 and E2, use:
=SUMIFS(B2:B6,A2:A6,">="&D2,A2:A6,"<="&E2)
The result is 280: 80 + 150 + 50. Both Jan 5 entries count because the end date is included. This is also a useful check when reviewing a result: filter the date column to the same range and confirm that the visible amounts add to the formula’s total.
For a quick one-off calculation, you can put dates directly in a formula using DATE, which avoids ambiguity between regional date formats. With dates in A2:A6 and amounts in B2:B6, this formula covers Jan 3 through Jan 5, 2025, inclusive, and returns 280 for the example above:
=SUMIFS(B2:B6,A2:A6,">="&DATE(2025,1,3),A2:A6,"<="&DATE(2025,1,5))
Cell references are generally more convenient for reports: changing D2 or E2 updates the result without editing the formula. You can also copy the formula to other report rows, provided the references are set up for the layout you need.
Add another condition to the date range
Often the goal is not to total every transaction in a period, but to total transactions for a particular department, product, or customer. Add another criteria-range and criteria pair to SUMIFS. For instance, suppose dates are in A2:A6, regions in B2:B6, amounts in C2:C6, and D2 and E2 hold the start and end dates. If F2 contains the region to include, the formula is:
=SUMIFS(C2:C6,A2:A6,">="&D2,A2:A6,"<="&E2,B2:B6,F2)
If D2 is Jan 3, E2 is Jan 5, and F2 is East, the result includes only East transactions on those dates. For example, if the qualifying East amounts are 80 and 150, the result is 230. Make sure each added criteria range covers the same rows as the date and sum ranges. For an exact text match, the value in F2 must match the region label in column B, including any extra spaces.
Handle dates that include times
Excel stores dates as serial numbers; a time is represented as a fractional part of a day. This matters when a date column contains timestamps such as Jan 5 at 3:00 p.m. A criterion of less than or equal to Jan 5 usually compares against midnight at the start of Jan 5. As a result, it can exclude transactions later that day even though they are on the intended end date.
For timestamp data, use a start boundary that includes the beginning of the first day and an exclusive boundary at the beginning of the day after the end date. With timestamps in A2:A100, amounts in B2:B100, and dates in D2 and E2, use:
=SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&(E2+1))
If D2 is Jan 3 and E2 is Jan 5, the formula includes times throughout Jan 5 but stops before Jan 6. This boundary style is often safer for timestamps than trying to specify the final possible time of a day. It also works for date-only values, since midnight on the next day is excluded.
Use this approach only when E2 contains a valid date and the intended end is the whole calendar day. If the user has supplied an exact end time, use that actual timestamp as the exclusive or inclusive boundary according to the reporting rule instead of automatically adding one day.
Can two SUMIF formulas do the same job?
They can, but SUMIFS is usually simpler. For date-only data, one way to express a range is to add everything up to and including the end date, then subtract everything before the start date. For dates in A2:A6 and amounts in B2:B6, a formula using Jan 3 and Jan 5 as the boundaries returns 280 for the worked example:
=SUMIF(A2:A6,"<="&E2,B2:B6)-SUMIF(A2:A6,"<"&D2,B2:B6)
This works because the second total removes rows earlier than the start. It is less straightforward to read, and timestamps require careful boundary choices: using an end criterion of less than or equal to a date can omit times later on that date. For a normal two-bound date filter, prefer SUMIFS; use the subtraction method only when you have a specific reason to work with separate cumulative totals.
Common problems and how to fix them
- The result is zero. Check that the date cells contain real Excel dates and that the boundaries are in the intended order. A date stored as text may look correct while failing a numeric date comparison.
- The total is too small on the end date. Check for time values in the date column. Use the exclusive next-day boundary described above to include all times on the final day.
- The formula returns an error. Confirm that the sum range and every criteria range have matching dimensions, and that parentheses and quotation marks are paired correctly. In a criteria expression, the comparison operator is written in quotes and joined to a cell reference with
&. - The answer changes unexpectedly when copied. Cell references move when a formula is filled down or across. If the date boundaries should stay fixed, use absolute references such as
$D$2and$E$2in the formula. - The total includes the wrong records. Verify the start and end cells, especially if dates are entered in a format that can be interpreted differently by regional settings. Display the dates in a clear format and compare them with the source records.
When diagnosing a formula, test a few rows manually: identify whether each date meets both conditions, then add the corresponding amounts. That can reveal a boundary or data-type problem faster than changing the formula at random. Also check for blanks or unexpected text in the amount column if the displayed total seems incomplete.
Frequently Asked Questions
Can SUMIF sum values between two dates?
Not with a single SUMIF condition. Use SUMIFS with one condition for the start date and one for the end date. A pair of SUMIF calculations can also be subtracted, but it is usually less direct.
Does the formula include the start and end dates?
Yes, when the criteria use greater than or equal to for the start and less than or equal to for the end. If the date column contains times, use a less-than criterion for the day after the end date to include every time on the final day.
How do I sum between dates in different cells?
Put the start and end dates in separate cells, then join each comparison operator to its boundary-cell reference with &. This lets you change the report period by editing the cells rather than rewriting the formula.
Can I include a category as well as dates?
Yes. Add a category range and its matching criterion as another pair in SUMIFS. The total then includes only rows that meet the date conditions and the category condition.
Why does Excel ignore dates that look valid?
The cells may contain text rather than Excel date values, or the formula may be comparing timestamps against a midnight boundary. Check the underlying data and use the next-day exclusive end boundary when times are present.
Related
- Excel Shortcut for Lowercase Text: What Actually Works
- Excel Shortcut Keys to Select a Column: Quick Guide
- Excel Shortcut to Select an Entire Column on Windows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Gtt per Min Formula: Examples and Safe CalculationThe gtt per min formula is gtt/min = (mL × drop factor in gtt/mL) ÷ time in minutes.…
- Excel Column Width to Match Row Height?Excel has no built-in setting that makes a column’s width equal a row’s height: column width is measured…
- Excel Shortcut to Drag a Formula DownIn Excel for Microsoft 365 on Windows, select the cell with the formula and the cells below it,…
- How to Delete an Array Formula in Excel Without ErrorsTo delete an array in Excel, remove its formula: select the entire output range for a legacy array…
- Excel Filter for Duplicate Values: Find and Review ThemTo filter duplicate values in Excel for Microsoft 365 on Windows, add a helper column with =COUNTIF($A$2:$A$100,A2)>1, fill…
- Excel NOW Time: Current Time, Formulas, and ShortcutsTo display the current date and time in Excel, enter =NOW() in a cell; to show only the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.