To sum values whose dates fall between two dates in Excel, use SUMIFS with the date column as a criteria range twice: once for the start date and once for the end date. For dates in A2:A100, amounts in C2:C100, a start date in E2, and an end date in F2, enter =SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2) to include both boundary dates.
What SUMIFS does
SUMIFS adds values only when every condition you specify is satisfied. It is useful for date-based totals because a date range can be expressed as two conditions applied to the same date column: the date must be on or after the start, and on or before the end.
The argument order matters. The first argument is the range to add; the remaining arguments come in criteria-range and criterion pairs. In the example above, C2:C100 is the amount range, while A2:A100 is tested against both date criteria. Each range should cover the same rows. If the amount range has 99 cells but a criteria range has a different number, Excel can return an error.
The two criteria are joined to their date values with the ampersand. The quotation marks make the comparison operators part of a text criterion, while E2 and F2 supply the actual date values. This is why a criterion such as greater than or equal to a cell is written as a quoted operator joined to that cell, rather than as a comparison typed directly into the formula.
Set up the dates and data
Put one transaction per row, with a genuine Excel date in the date column and a numeric value in the amount column. Enter the desired start and end dates in separate cells; that makes the period easy to change without editing the formula. For example, E2 could contain January 1, 2025, and F2 could contain January 31, 2025.
Excel stores dates as serial numbers and displays them using a date format. A value that merely looks like a date may instead be text, particularly if it was imported from another system. To check the setup, select a date cell and apply a date format from Home > Number Format. Changing the display format does not convert text into a real date, though; text dates may need to be converted before the criteria will work as expected.
- Use one consistent date column for the transaction dates.
- Keep the amount range and date range aligned row by row.
- Make sure the start and end cells contain valid dates, not labels typed as text.
- Check that the start date is earlier than or equal to the end date.
Use inclusive start and end dates
The most common interpretation of “between two dates” includes the first and last day. The formula in the opening paragraph does that: ">="&E2 includes records on the start date, and "<="&F2 includes records on the end date.
Suppose rows 2 through 6 contain the following dates and amounts: January 1, $120; January 10, $85; January 31, $300; February 1, $45; and January 31, $700. With E2 set to January 1 and F2 set to January 31, the result is $1,205: 120 + 85 + 300 + 700. The February 1 transaction is outside the selected period. This example assumes all five amounts are numeric and the dates are actual Excel dates.
For that sample layout, with dates in A2:A6, amounts in C2:C6, and the period boundaries in E2 and F2, the formula returns 1205:
=SUMIFS(C2:C6,A2:A6,">="&E2,A2:A6,"<="&F2)
In HTML, the ampersands are displayed as & so the fragment remains valid; the Excel formula itself uses ordinary ampersand characters. When entering it directly in Excel, type =SUMIFS(C2:C6,A2:A6,">="&E2,A2:A6,"<="&F2) with regular ampersands. Excel’s formula bar will show the normal formula syntax.
Handle times stored with dates
A date-time value can include a time fraction. For instance, a transaction recorded at 3:00 p.m. on January 31 is later than January 31 at midnight. If the end criterion is less than or equal to the end-date cell, that afternoon record can be excluded when the cell represents midnight.
For a period that should include every time on the end date, use a strict less-than test against the day after the end date. With dates and times in A2:A100, amounts in C2:C100, and the start and end dates in E2 and F2, this formula includes the full end date:
=SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<"&F2+1)
This works because Excel date-time values are stored as numbers, with the time represented as a fraction of a day. All values on the end date are less than midnight at the beginning of the following day. The strict less-than boundary also avoids trying to guess the last possible time value for that date.
Use this approach when the date column may contain timestamps, even if the cells are formatted to display only the date. If the data is guaranteed to contain dates without times, the inclusive less-than-or-equal formula is straightforward and appropriate.
Use fixed dates or other criteria
Cell references are convenient when users need to change the reporting period. For a one-off calculation, you can instead create the boundaries with Excel’s DATE function. With transaction dates in A2:A100 and values in C2:C100, this example sums the inclusive 2025 calendar year:
=SUMIFS(C2:C100,A2:A100,">="&DATE(2025,1,1),A2:A100,"<="&DATE(2025,12,31))
The DATE function takes year, month, and day as numbers, so this method avoids relying on how a typed date is interpreted under different regional date settings. For a data set that includes times, change the end test to less than January 1 of the following year. That boundary includes every time on December 31.
You can add further criteria pairs when a report needs to filter by another field as well as date. For example, a department or product criterion can be added after the date criteria. Each additional pair narrows the rows that qualify; it does not create an alternative condition. All specified conditions must be true for a row to contribute to the sum.
Common problems and how to fix them
A zero result does not always mean the formula is wrong. It can mean that no rows meet all the criteria, or that Excel does not recognize the date values as dates. Check a few source cells, confirm the selected period, and verify that the criteria ranges point to the intended columns.
- Dates are stored as text: Convert imported text dates into Excel date values. Formatting alone changes appearance, not the underlying value.
- End-date transactions are missing: If the date column contains times, use the less-than-next-day boundary described above.
- The formula returns an error: Make sure all ranges have matching dimensions and that the arguments are in the correct order.
- The total is unexpectedly high or low: Check for duplicate transactions, blank or incorrect date cells, and an amount range that starts or ends on the wrong row.
- The formula shows as text: Check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign. Change the cell format to General, then re-enter the formula.
Excel for Microsoft 365 on Windows commonly uses commas between formula arguments, as shown here. In some regional configurations, Excel may use semicolons as the list separator; if commas produce a formula-entry error, use the separator configured for that installation. Excel for Mac and Excel for the web use the same SUMIFS logic, although regional separators and interface details can vary.
SUMIFS compared with other approaches
SUMIFS is usually the clearest choice when a total depends on multiple conditions, such as a date period plus a department. It is a built-in worksheet function and recalculates as the source data or boundary cells change. For just one condition, SUMIF is available, but a two-sided date range needs both a lower and an upper criterion, making SUMIFS a natural fit.
Filtering a table by date and using a manual total can help with a quick inspection, but it is less suitable for a repeatable report. A PivotTable is another useful option when you want to group totals by month, year, category, or other fields interactively. The best choice depends on the output: use SUMIFS for a formula-driven total in a report cell, and consider a PivotTable for flexible grouping and exploration.
A common misconception is that Excel needs a special “between” operator for SUMIFS. It does not. Two criteria on the same date range express the lower and upper limits. Another misconception is that dates must be entered in a particular visual format; the important point is that the cell contains a real date value, not how that value is displayed.
Make the formula easier to maintain
For a small fixed list, ordinary cell ranges are easy to understand. If data will grow over time, format it as an Excel table using Insert > Table. Table columns expand as rows are added, and structured references can make formulas easier to read. When writing a structured-reference version, confirm the actual table and column names because they depend on the workbook.
Keep the start and end dates visible near the result, and label them clearly. If the formula is copied to other locations, decide whether those boundary references should move; use absolute references such as $E$2 and $F$2 when they must stay fixed. For a recurring report, consider a separate input area and a clearly labeled total so that changing the reporting period does not require editing formula text.
When auditing a total, temporarily filter the source table to the same period and compare the visible transactions. This is not a substitute for checking the formula, but it can reveal an unexpected date, a timestamp, or a duplicate row. Also verify the workbook’s calculation mode if results do not update after changing a date; Excel’s calculation settings are available from Formulas > Calculation Options.
Frequently Asked Questions
Does SUMIFS include both dates in the range?
Yes, if you use a greater-than-or-equal start criterion and a less-than-or-equal end criterion. If the date cells include times, use a strict less-than test against the day after the end date to include the entire final day.
Why does SUMIFS return zero between my dates?
Check that the source dates and boundary cells are real Excel dates, the period is in the right order, the criteria ranges match the sum range in size, and at least one row meets both boundaries. Imported dates stored as text are a frequent cause.
Can I sum between dates and match a category too?
Yes. Add another criteria-range and criterion pair for the category column. SUMIFS requires every condition to be true, so the result includes only rows within the date period that also match the selected category.
How do I include all transactions on the end date when times are present?
Use the start date with a greater-than-or-equal criterion and use a less-than criterion against the end date plus one. This includes all times during the end date without needing to specify a final time.
Can I use SUMIFS for a whole month?
Yes. Set the start boundary to the first day of the month and the end boundary to the last day, or use the first day of the next month as an exclusive upper limit. The latter is especially reliable when the source dates include times.
Related
- Randomize Groups in Excel: A Practical Guide
- Randomize in Excel Without Repeating Numbers: 3 Ways
- Convert Text to Number in Excel Quickly
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sequence Number Shortcut: Fast Ways to Fill a SeriesExcel has no single dedicated shortcut key that automatically numbers a sequence. In Microsoft 365 for Windows, enter…
- How to Add a Formula in Excel for Addition: Simple StepsTo add numbers in Excel, select the cell where you want the answer, type a formula that starts…
- Average Equation in Excel: Formulas, Uses, and ExamplesThe average equation in Excel is =AVERAGE(range). For example, with numbers in B2:B6, enter =AVERAGE(B2:B6) in another cell…
- Excel VLOOKUP Column Index Number ExplainedIn Excel VLOOKUP, the column index number tells Excel which column to return a value from, counting from…
- Can’t Filter in Excel? Causes, Fixes, and WorkaroundsYes—Excel can filter a list or table, but the Filter command may be unavailable, the dropdown arrows may…
- Inventory Management in Excel Using VLOOKUP: Setup GuideYes. Create a product master with each unique SKU in its leftmost column, then use VLOOKUP to bring…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.