To sum values for dates within an inclusive start-to-end range in Excel, use SUMIFS, which supports more than one condition. With dates in A2:A100, amounts in B2:B100, the start date in D2, and the end date in E2, enter =SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2).
Why SUMIFS is the right choice
Although people often search for “SUMIF between two dates,” the standard solution uses SUMIFS (with an s). A date range has two conditions: the date must be on or after the starting date, and it must be on or before the ending date. SUMIF accepts one condition; SUMIFS can apply several conditions to the same date column while adding the corresponding values.
In SUMIFS, the sum range comes first, followed by pairs of criteria ranges and criteria. In the formula above, B2:B100 is the range to total, and A2:A100 is used twice: once to test the lower bound and once to test the upper bound. The symbols are joined to the cell references with &. That joins each comparison operator to the date value stored in D2 or E2.
The range endpoints are inclusive in this version: a record dated exactly on the start date or end date is counted. This is usually what people intend when they say “between these dates.” If the start and end dates are not meant to count, change the comparison operators to strict greater-than and less-than signs.
Set up the dates and amounts
Keep each transaction date in one column and the numeric value to add in another. Put the two boundary dates in separate cells so they can be changed without editing the formula. For example, suppose a small sales list contains these records:
- May 31 — 200
- June 1 — 120
- June 15 — 85
- June 30 — 40
- July 1 — 75
With the transaction dates in A2:A6, the amounts in B2:B6, June 1 in D2, and June 30 in E2, the formula is =SUMIFS(B2:B6,A2:A6,">="&D2,A2:A6,"<="&E2). It returns 245: 120 + 85 + 40. The May and July records fall outside the chosen range.
Enter actual Excel dates in D2 and E2, rather than relying on text that merely looks like a date. You can type a date in a format Excel recognizes for your regional settings, then format the cell as a date using Home > Number. The display format can differ from the stored value; a date may appear as “1-Jun” while Excel stores it as a date serial number that formulas can compare.
Handle date-and-time values correctly
A common complication is that transaction cells may contain both a date and a time. Excel stores a date-time as a number with a fractional part for the time. For instance, a record on June 30 at 3 p.m. is later than June 30 at midnight. As a result, an inclusive upper test of “less than or equal to June 30” can exclude that record if E2 contains June 30 at midnight.
For a range that should include every time on the ending date, use an exclusive upper boundary at the start of the next day. With date-time stamps in A2:A100, amounts in B2:B100, and the start and end dates in D2 and E2, use:
=SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&(E2+1))
This includes times from midnight at the start date up to, but not including, midnight after the end date. It works for date-only values too. Use this approach when the end date is intended to be fully covered and the date column may contain times. If your data is guaranteed to contain dates with no time component, the earlier less-than-or-equal formula is clear and appropriate.
Use a date range that changes automatically
For a one-off calculation, entering the two boundary dates in cells is often easiest. Cell-based criteria are also useful for reports: change the dates in D2 and E2, and the total updates without rewriting the formula. Make sure the start date is not later than the end date; otherwise the conditions cannot both be true for any ordinary date.
You can also define a period using date functions. For example, if D2 contains any date in the month you want to report, the first day of that month can be calculated with =DATE(YEAR(D2),MONTH(D2),1), and the first day of the following month with =EDATE(DATE(YEAR(D2),MONTH(D2),1),1). With dates in A2:A100, amounts in B2:B100, and the month selector in D2, this formula sums the selected month using a next-month exclusive boundary:
=SUMIFS(B2:B100,A2:A100,">="&DATE(YEAR(D2),MONTH(D2),1),A2:A100,"<"&EDATE(DATE(YEAR(D2),MONTH(D2),1),1))
For example, if D2 contains any date in June 2026, the criteria cover June 1 through June 30, including any times recorded on June 30. EDATE advances a date by a specified number of months, avoiding the need to hard-code the number of days in each month. This formula also handles February and leap years without a manually chosen month length.
Check common formula problems
If the result is zero or unexpectedly low, check the data and criteria before replacing the formula. Dates imported from another system may be text, even when they look like dates. Text values do not behave like Excel date serials in comparisons. A quick check is to change a suspect cell’s number format to General: a real date normally displays as a number, while text generally remains text. Converting imported text may require using Data > Text to Columns or correcting the source data; inspect the results before relying on a conversion.
- Confirm matching range sizes. The sum range and each criteria range should cover corresponding rows. A2:A100 and B2:B100 are aligned; using A2:A100 with B3:B101 would pair dates with the wrong amounts.
- Check for times. If the date column contains timestamps, use the next-day exclusive upper boundary described above.
- Check the boundary cells. Ensure D2 and E2 contain real dates, and verify that the start is no later than the end.
- Look for values stored as text. SUMIFS adds numeric cells. Amounts imported with currency symbols or other text may need cleaning or conversion.
- Review the criteria punctuation. The comparison operator must be joined to the cell reference, as in
">="&D2; putting the whole expression in quotes would search for literal text instead.
Excel normally uses commas between formula arguments in Microsoft 365 for Windows with typical English-language settings. Some regional configurations use semicolons instead, based on their list-separator settings. If Excel rejects a formula that otherwise looks correct, check the separator expected by your installation. The formula logic is the same.
SUMIF, SUMIFS, and other options
Use SUMIFS for two date limits and whenever a total also needs other conditions, such as a department, product, or status. For example, you can add a department criterion as another range-and-criteria pair, provided you use the same row alignment. This keeps the calculation in one formula and makes each condition visible.
SUMIF remains useful when there is only one condition, such as summing all sales on or after a date. But forcing two date bounds into a single SUMIF is less direct and can lead to unnecessarily complicated formulas. For a standard date interval, prefer SUMIFS. A PivotTable is another good option when you want to group many records by month or year, explore categories, or summarize a larger list interactively; for one total controlled by two date cells, the formula is usually simpler.
In Microsoft 365 for Windows, enter the formula in a blank cell and press Enter. The same formulas work in Excel for Mac; the interface and some keyboard shortcuts differ, but no special Mac version of the formula is needed. In Excel for the web, the formula syntax is also supported, although menu labels and editing controls can vary slightly from the desktop application.
Make the calculation easier to maintain
Use clear labels such as “Start date” and “End date” beside the boundary cells, and format those cells consistently. If the transaction list grows, consider converting it into an Excel table with Insert > Table. Tables expand as you add records, which can make formulas easier to maintain than fixed ranges. Structured references may make a formula more readable, but the basic requirement remains the same: one date column supplies both criteria, and the amount column supplies the values to total.
For a single report, ordinary ranges such as A2:A100 are easy to inspect. For a regularly updated workbook, a table can reduce the chance that newly added rows are left out. Whichever structure you choose, avoid including headings in the ranges and keep dates and amounts in their own columns. Those habits make it easier to audit the total and spot misaligned data.
Frequently Asked Questions
Can I use SUMIF for dates between two dates?
Use SUMIFS instead for the usual solution. The range needs both a lower-bound and an upper-bound condition, while SUMIF accepts one condition. SUMIFS is designed to combine those criteria and sum the matching amounts.
Does SUMIFS include the start and end dates?
Yes, if the criteria use >= for the start and <= for the end. If the date column contains times and you want to include the entire end date, use a less-than test against the day after the end date instead.
Why does my date-range formula return zero?
Common causes include dates stored as text, criteria cells that are text rather than real dates, an incorrect range reference, or amounts stored as text. Also check whether the formula’s date and amount ranges cover the same rows.
Can I sum between dates in different years?
Yes. The formula compares full Excel date values, including their years, so a start date in one year and an end date in another work normally as long as the start is earlier than the end.
Can I add another condition, such as a salesperson?
Yes. Add another criteria-range and criteria pair to SUMIFS, using the salesperson column as the range and a cell containing the salesperson’s name as the criterion. Keep the added range aligned with the date and amount rows.
Related
- Excel Repair Software Free Full Version: What Works?
- Excel Repair Tools for XLS and XLSX Files: What Works
- Excel Shuffle List of Names: 10 Easy Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP Examples: Exact, Approximate, and Wildcard MatchXLOOKUP’s match_mode argument controls whether Excel finds an exact value, a nearby value, or a wildcard pattern. For…
- Count Excel Rows After a Filter: Formulas That WorkTo count rows currently visible after filtering, use SUBTOTAL on a column that has a value in every…
- Excel Minutes Between Two Times: Formula and ExamplesIf the start time is in A2 and the end time is in B2, enter =(B2-A2)*1440 to calculate…
- Excel FILTER Function: Filter Rows by Partial TextTo return rows where a column contains part of a word or phrase, combine Excel’s FILTER function with…
- Excel AGGREGATE Formula: Two Practical Examples ExplainedUse Excel’s AGGREGATE function to calculate a result while choosing whether to ignore errors, hidden rows, or both.…
- Rank in Excel by Multiple Criteria: Formulas and TipsWith primary scores in B2:B5, secondary scores in C2:C5, and a third tie-breaker in D2:D5, enter this in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.