To sum values in Excel for dates from a start date through an end date, use =SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2), where column A contains dates, column C contains the values to add, and E2 and F2 hold the boundary dates. The criteria include both boundary dates, so records on the start and end dates are counted.
The basic SUMIFS formula
SUMIFS adds numbers that meet one or more conditions. Its first argument is the range to sum; the remaining arguments come in pairs: a range to check and the criterion for that range. For a date interval, the date column is used twice—once to set the earliest permitted date and once to set the latest.
With dates in A2:A100, amounts in C2:C100, the start date in E2, and the end date in F2, enter this formula in a result cell:
=SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2)
In Excel, the ampersand joins each comparison operator to the date value in its cell. The criteria therefore mean “date is greater than or equal to E2” and “date is less than or equal to F2.” Both conditions apply to the same date range, so a row is added only if its date falls within the interval.
How Excel evaluates date criteria
Excel stores actual dates as serial numbers, even though the worksheet usually displays them in a familiar format such as 1/15/2025. SUMIFS compares those underlying values. The formula must therefore compare real Excel dates, not date-looking text that Excel has failed to recognize as a date.
The operators matter. “>=” makes the start date inclusive, while “<=” makes the end date inclusive. The quotation marks around each operator are part of the criteria syntax, and the ampersand attaches the operator to a cell reference. Do not type the operator and date cell together as a phrase such as ">=E2": that would be treated as literal text rather than a reference to E2.
Using cell references makes the report easier to reuse. Change E2 or F2 and the result updates without editing the formula. Format those cells as dates so people can see the selected period clearly; formatting changes how a date appears, not the date value SUMIFS evaluates.
Set up and check a date-range example
Suppose A2:A6 contains five transaction dates and C2:C6 contains their amounts:
- January 1, 2025: $120
- January 15, 2025: $80
- January 31, 2025: $50
- February 1, 2025: $40
- January 15, 2025: $25
With the start date 1/1/2025 in E2 and the end date 1/31/2025 in F2, use this version for the five-row example:
=SUMIFS(C2:C6,A2:A6,">="&E2,A2:A6,"<="&F2)
The result is $275: $120 + $80 + $50 + $25. The February 1 transaction is outside the range and is not added. This check is useful when validating a report: compare the total with a few known records on the boundary dates as well as records clearly outside the period.
To enter the dates, type them into E2 and F2 in a format Excel recognizes, or select the cells and choose a date format from Home > Number Format. The displayed format can differ from the way you type a date, depending on regional settings. If Excel interprets an entry unexpectedly, inspect the cell value and regional date order before relying on the total.
Include every time on the end date
The basic formula is right when the date cells contain dates without times. A date with a time is stored as a number with a fractional part: for example, a transaction at noon is later than midnight on that calendar date. As a result, a criterion of “less than or equal to” the end date usually includes midnight at the beginning of that day, but excludes later times that day.
For records that may contain timestamps, use an exclusive upper boundary: include dates from the start date onward, but stop before the day after the end date. With dates or timestamps in A2:A100, amounts in C2:C100, a start date in E2, and an end date in F2, use:
=SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<"&F2+1)
This includes every time on the end date while excluding midnight on the following date. For example, if F2 is January 31, a timestamp at 11:59 p.m. on January 31 qualifies, while one at midnight on February 1 does not. This pattern is often safer for exported transaction logs, event records, and other data that includes times.
Sum a month or another changing period
If the report should cover the whole month containing a selected start date, you can calculate the next month’s boundary rather than manually entering the final day. With dates in A2:A100, amounts in C2:C100, and any date in the target month in E2, this formula sums from E2 up to, but not including, the same day in the following month:
=SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<"&EDATE(E2,1))
For a monthly report, put the first day of the month in E2; the result then covers that calendar month, including all times on its last day if the data contains timestamps. EDATE advances by a specified number of months and handles months with different lengths. If E2 is not the first of the month, this formula starts on that selected day instead, so use a month-start date when the intention is to total a full calendar month.
For a fixed quarter, fiscal year, or custom period, the same start-and-end approach works: put the correct boundary dates in cells and use the inclusive formula for date-only data, or the exclusive next-day boundary for timestamps. The spreadsheet does not infer a fiscal calendar; the chosen dates must reflect the reporting rules you intend to use.
Add another condition to the date range
SUMIFS can combine the date interval with other filters, such as a department, product, or salesperson. Each extra condition adds another criteria-range and criteria pair. All conditions must be true for a row to contribute to the total.
For example, suppose dates are in A2:A100, salesperson names in B2:B100, amounts in C2:C100, the start and end dates are in E2 and F2, and the requested salesperson is in G2. Use:
=SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2,B2:B100,G2)
This adds only amounts whose dates fall within the selected interval and whose salesperson matches G2. If the data contains timestamps, change the end-date condition to the exclusive boundary "<"&F2+1 as described above. Keep the sum range and every criteria range aligned: they should cover the same rows and have matching dimensions, or the result can be wrong or Excel may return an error.
Common problems and how to fix them
- The result is zero. Check that E2 is no later than F2, the source dates are real dates, and the boundary cells are not text. Also check that the date range actually contains values within the selected interval.
- The last day appears to be missing. If the source contains times, use the exclusive next-day criterion rather than “less than or equal to” the date at midnight.
- The formula returns an error. Confirm that each range covers the same number of rows and that every SUMIFS criteria range has a corresponding criterion. In Excel for Microsoft 365 on Windows, commas are the standard argument separators in the formulas shown here; some regional configurations display a different list separator.
- The result is too large or too small. Look for duplicate source rows, values stored as text, or dates outside the intended reporting period. A filter or a PivotTable can help inspect which records contribute, but verify the source data rather than changing the formula to hide discrepancies.
- The formula changes when copied. Relative references such as A2:A100 move when copied. For a formula copied across a report, lock fixed ranges with dollar signs, for example $A$2:$A$100, while leaving criteria cells relative or locking them according to the intended layout.
One common misconception is that changing a cell’s display format converts text into a real date. It does not. If dates arrived as text from a CSV or another system, convert or re-import them as dates before depending on date comparisons. A quick visual check is not conclusive because text can look exactly like a date.
SUMIFS compared with other ways to total dates
SUMIFS is usually a direct choice when the source is a flat list and the goal is to total one numeric column by date boundaries, optionally with additional conditions. Its criteria are visible in the formula, and date limits can be controlled from worksheet cells. It is also available in current desktop Excel, Excel for Mac, and Excel for the web; the formula logic is the same across those versions.
AutoSum is useful for adding an adjacent block of numbers, but it does not by itself express a date condition. A PivotTable is a better fit when readers need to explore totals by several categories, group dates, or rearrange a report interactively. For a single calculated total that responds to start and end date cells, SUMIFS is often simpler and easier to audit.
For date-only source data, use inclusive start and end criteria. For timestamps, use a start-inclusive and next-day-exclusive interval. Choosing the boundary style based on the actual data is more important than choosing between a formula and a report feature: the wrong upper boundary can silently omit legitimate end-of-day records.
Frequently Asked Questions
Can SUMIFS use dates stored in cells?
Yes. Join the comparison operator to the date cell with an ampersand, such as the start-date criterion ">="&E2. The referenced cell must contain a real Excel date value for the comparison to work reliably.
Does SUMIFS include the start and end dates?
With criteria of greater than or equal to the start and less than or equal to the end, it includes both dates when the source contains date-only values. For timestamps, use a less-than comparison against the day after the end date to include all times on the final day.
How do I sum between dates when the dates are in another column?
Use the date column as both criteria ranges, and use the amount column as the sum range. For example, if dates are in A and amounts are in C, the formula compares A to both boundaries and adds matching values from C.
Can I use SUMIFS for a whole month?
Yes. Set the start to the first day of the month and use the first day of the next month as an exclusive upper boundary. That approach includes every date and timestamp in the target month without needing to know whether it has 28, 30, or 31 days.
Why does my SUMIFS date formula return zero?
Common causes include text dates, reversed start and end dates, a date range that does not contain the selected period, or criteria ranges that do not align with the sum range. Check the underlying values and test the formula against a small group of known rows.
Related
- Excel Reverse Worksheet Order: How to Do It
- Excel Repair Software Free Full Version: What Works?
- Excel Repair Tools for XLS and XLSX Files: What Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- HLOOKUP in Excel: Purpose, Examples, and LimitsHLOOKUP in Excel searches for a value across the top row of a table and returns a related…
- Excel Transpose Bottom to Top: Formulas and Fixes GuideTo transpose a vertical list from bottom to top into a horizontal row in Excel for Microsoft 365,…
- Excel Concatenate Dates While Keeping Formatting in TextTo concatenate a date in Excel and keep its display format, convert the date to formatted text with…
- Excel Formula to Randomly Select From a ListWith names in A2:A20, use =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))) to return one randomly chosen entry. The formula selects a row position…
- Excel Shortcut for Removing a Filter: What WorksIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter off or on; this removes the…
- Excel Color Filter: How to Filter Rows by Cell ColorTo filter rows by color in Excel for Microsoft 365 on Windows, select a cell in your data,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.