To sum values for a date or date range in Excel, use SUMIF with a date criterion; for a range with both a start and end date, use SUMIFS. For example, with dates in D2:D6 and amounts in E2:E6, use =SUMIFS(E2:E6,D2:D6,">="&DATE(2025,2,1),D2:D6,"<"&DATE(2025,3,1)) to total February 2025.
How SUMIF works with dates
SUMIF adds numbers when corresponding cells meet one condition. Its syntax is SUMIF(range, criteria, [sum_range]): range contains the values Excel checks, criteria defines the condition, and sum_range contains the numbers to add. For dates, the checked range must contain genuine Excel date values, not just text that looks like dates.
Excel stores a date as a number and displays it using a date format. A date criterion therefore compares underlying numbers, not the appearance of the cells. A date displayed as 1-Feb-2025 can still be compared with another valid date value even if the other cell displays as 2/1/2025. The display format does not change the date being compared.
For the examples below, suppose D2:D6 contains January 5, January 15, January 31, February 1, and February 15, 2025, in that order. E2:E6 contains 120, 80, 200, 50, and 150. The corresponding amounts are summed when their dates meet the criterion. The examples use commas as Excel’s argument separators, as is standard in many regional settings; some installations use semicolons instead.
Sum values on one exact date
To add amounts recorded on one particular date, set the criterion to that date. With dates in D2:D6 and amounts in E2:E6, this formula sums transactions on January 15, 2025, and returns 80:
=SUMIF(D2:D6,DATE(2025,1,15),E2:E6)
Using DATE(year,month,day) avoids ambiguity between regional date formats. In contrast, typing a date as text inside a formula can be misread when workbooks move between computers configured for different locales. If the date is already in a worksheet cell, you can refer to that cell as the criterion rather than hard-coding it.
An exact-date match has an important limitation: it matches the date value exactly. If the cells also contain times, a value such as January 15 at 9:00 AM is a different underlying number from January 15 at midnight. Use a one-day range instead when the source data may include times.
Sum between two dates
SUMIF supports one condition, so it cannot by itself require a date to be both on or after a start date and before an end date. Use SUMIFS, which allows multiple conditions. With dates in D2:D6 and amounts in E2:E6, the February formula in the opening paragraph returns 200: 50 for February 1 plus 150 for February 15.
The criteria ">="&DATE(2025,2,1) includes the start date, while "<"&DATE(2025,3,1) excludes the first day of March. This inclusive-start, exclusive-end pattern is useful because it includes every time on dates in February if the date cells contain timestamps. It also avoids having to invent a final time such as 11:59:59 PM.
For a range where the end date itself should be included, use a less-than test against the day after that end date. For example, to include February 1 through February 15, use this formula. With dates in D2:D6 and amounts in E2:E6, it returns 200:
=SUMIFS(E2:E6,D2:D6,">="&DATE(2025,2,1),D2:D6,"<"&DATE(2025,2,16))
That approach also includes times occurring during February 15. The formula’s upper bound is February 16 at midnight, which excludes later dates without excluding times earlier on the 15th.
Use dates stored in worksheet cells
Cell references make a date-based total easier to change and reuse. Suppose A2 contains a genuine Excel date representing February 1, 2025. With dates in D2:D6 and amounts in E2:E6, this formula adds amounts on or after A2 and returns 200 for the sample data:
=SUMIF(D2:D6,">="&A2,E2:E6)
The ampersand joins the comparison operator to the date value in A2, creating a criterion such as “greater than or equal to” that date. The cell reference can point to an input cell that users change, but format that cell as a date so the value is easy to read. Formatting is for clarity; the cell must still contain an actual date value.
For a start date in A2 and an end date in B2, use both references as conditions. With dates in D2:D6 and amounts in E2:E6, and A2 set to February 1 and B2 set to February 15, this returns 200, including both boundary dates and any times on the end date:
=SUMIFS(E2:E6,D2:D6,">="&A2,D2:D6,"<"&B2+1)
Adding 1 to a date advances it by one day. This formula assumes A2 and B2 are valid dates and that B2 is not earlier than A2. If the end date is meant to be exclusive—for example, a reporting period ending at the start of February 15—use "<"&B2 rather than adding 1.
Handle timestamps and monthly totals
Excel represents a time as a fraction of a day, so a timestamp includes a date portion plus a time portion. That is why an exact-date SUMIF can miss entries made later on that same day. To include all records for January 15, set an inclusive lower limit at that date and an exclusive upper limit at the next day.
Suppose D2:D4 contains January 15, 2025 at 8:30 AM, January 15 at 3:00 PM, and January 16 at midnight, while E2:E4 contains 10, 25, and 40. This formula returns 35, including both January 15 entries but not the midnight entry on January 16:
=SUMIFS(E2:E4,D2:D4,">="&DATE(2025,1,15),D2:D4,"<"&DATE(2025,1,16))
The same boundary method works for a month or year: use the first day of the period as the lower limit, and the first day of the following period as the exclusive upper limit. It is generally more reliable than trying to match a month name or a displayed date string. It also works when the dates include times.
Common problems and how to fix them
A formula can return zero or an unexpected total even when the dates look correct on screen. Check the underlying values and the shape of the ranges before changing the formula.
- Dates stored as text: Text that looks like a date may not compare as a numeric Excel date. Re-enter or convert the source values to dates, then verify that changing the cell’s number format changes how the value displays. Merely applying a date format does not convert text into a date.
- Dates include times: Exact equality may omit entries with non-midnight times. Use a lower and upper date boundary with
SUMIFSto cover the whole day or period. - Criteria operator is not joined to the date: When the operator comes from a formula and the date comes from a cell or function, join them with
&, as in">="&A2. A criterion written as text must also put the operator inside the quoted text. - Range sizes do not match: The criteria range and sum range should cover corresponding rows. For example, D2:D100 paired with E2:E99 can produce incorrect results or an error.
- Wrong date interpretation: A string such as 03/04/2025 may mean March 4 or April 3 depending on regional settings. Prefer a date cell or the
DATEfunction when building formulas. - Hidden spaces or mixed data: Check that the date column contains dates rather than a mixture of dates, text, and blanks. Sorting or filtering the column can help reveal suspicious entries, but a displayed date format alone is not proof that every value is valid.
If a total is too large, confirm that the formula’s lower and upper limits are in the intended order and that the end boundary is correct. A criterion of “on or after” the start date alone includes every later date in the range, not just dates within the desired month. Also check whether duplicate rows are present; a sum formula adds every matching row.
SUMIF, SUMIFS, and other ways to summarize dates
Use SUMIF when one condition is enough, such as a single date or all dates after a chosen cutoff. Use SUMIFS when a total must satisfy a start and end date, or when it must also meet another condition such as a department or product. In SUMIFS, the sum range comes first, followed by pairs of criteria ranges and criteria.
For instance, a report may need to total February sales for one region. That requires a date-start condition, a date-end condition, and a region condition, so SUMIFS is the appropriate choice. Keep each criteria range aligned with the sum range. If the task is to count matching dates rather than add amounts, use a counting function such as COUNTIFS for multiple conditions.
For recurring reports, a PivotTable can summarize a date field by month, quarter, or year without writing a separate formula for each period. This is useful for exploring totals across categories, while SUMIF and SUMIFS are convenient when a worksheet needs a specific calculated result. A PivotTable groups dates only when Excel recognizes the source values as dates; text dates should be corrected first. The formula examples here work in Excel for Microsoft 365 on Windows; the functions and formula logic are also available in Microsoft 365 for Mac and Excel for the web, though interface details can differ.
Frequently Asked Questions
Can SUMIF sum values for a date range?
Not with two independent date limits in one SUMIF condition. Use SUMIFS with a start-date condition and an end-date condition. For an inclusive end date that may contain times, use a less-than condition for the following day.
Can I use a date cell as the SUMIF criterion?
Yes. A cell containing a valid Excel date can be used directly for an exact match, or joined to a comparison operator with & for a range boundary. Make sure the referenced cell is a date value rather than date-looking text.
Why does SUMIF return zero for a date that is visible in the list?
The date may be stored as text, may include a time that prevents an exact match, or may not be the same underlying date as the criterion. Check the source values, regional interpretation, and whether a whole-day date range is needed.
How do I sum a whole month with SUMIF?
For a month bounded by two dates, use SUMIFS: include dates on or after the first day of the month and dates before the first day of the next month. This pattern handles all days in the month and any times stored with those dates.
Does formatting a cell as a date fix a text date?
No. Number formatting changes how a value is displayed; it does not reliably convert text into an Excel date. Convert or re-enter text dates as actual date values before relying on date criteria.
Related
- Excel Shortcut to Select Every Cell in a Column on Windows
- Excel Shortcut for Lowercase Text: What Actually Works
- Excel Shortcut Keys to Select a Column: Quick Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for File Path: Get the Workbook LocationIn Excel for Microsoft 365 on Windows, use =CELL("filename",A1) to return the saved workbook’s path, file name, and…
- Number Sequence in Excel: How to Continue a SeriesTo continue a number sequence in Excel, enter the first two values, select both cells, then drag the…
- Filter Dates in Excel Oldest to Newest: A Step-by-Step GuideTo put dates in chronological order in Excel for Microsoft 365 on Windows, select a cell in the…
- Calculate Age in Excel with DATEDIF: Formula and TipsTo calculate someone’s age in completed years in Excel, enter their birth date in A2 and use =DATEDIF(A2,TODAY(),"Y").…
- Excel Value Comparison Formula: Examples and OptionsIn Excel, compare two values with =A2=B2: it returns TRUE when the values match and FALSE when they…
- Excel IFS Between Two Dates: Formulas and ExamplesTo check whether a date falls between two dates in Excel, put an AND test inside IFS: IFS(AND(date>=start,date<=end),result,TRUE,other_result).…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.