Excel SUMIFS Date Range Not Working? Fix the Criteria

If Excel SUMIFS is not summing a date range, first check that the dates are real Excel dates and that your criteria join comparison operators to date values. For a range that includes every time on the ending date, use a start-date test and an exclusive test for the day after the end date, such as ">="&E2 and "<"&F2+1.

A reliable SUMIFS date-range formula

Assume transaction dates are in A2:A6, amounts are in C2:C6, the start date is in E2, and the end date is in F2. With E2 set to January 1, 2025, and F2 set to January 31, 2025, this formula includes all transactions on those dates, including entries that have times attached:

=SUMIFS($C$2:$C$6,$A$2:$A$6,">="&E2,$A$2:$A$6,"<"&F2+1)

For example, if the records are January 5 for 120, January 20 for 300, January 31 at 3:30 p.m. for 45, and February 1 for 80, the result is 465. The February record is outside the range. The formula uses the same date range twice: once to require a date on or after the start, and once to require a date before the day following the end.

If your date column contains dates with no time component, an inclusive end-date test can also work. The exclusive-boundary approach is usually safer because it also includes end-date records whose times are later than midnight.

How SUMIFS evaluates date criteria

SUMIFS adds values from a sum range only when corresponding cells meet every supplied condition. Its basic structure is SUMIFS(sum_range, criteria_range1, criteria1, ...). Each criteria range must align with the sum range: the same number of rows and columns, with each row representing the same record. A mismatched range can produce an error or misleading results.

Excel stores valid dates as serial numbers. A date criterion such as “on or after January 1” is therefore a numeric comparison, even though cells are usually displayed as calendar dates. The comparison operator must be part of the criteria text, while the date value is joined to that text with the ampersand. That is why ">="&E2 is different from typing ">=E2": the latter asks Excel to compare against the literal text E2 rather than the value in that cell.

For a fixed range without input cells, assume dates are in A2:A6 and amounts are in C2:C6. This version sums dates from January 1 up to, but not including, February 1, 2025:

=SUMIFS($C$2:$C$6,$A$2:$A$6,">="&DATE(2025,1,1),$A$2:$A$6,"<"&DATE(2025,2,1))

With the example records above, it returns 465. Using DATE avoids ambiguity between regional date formats such as month/day/year and day/month/year. It also makes the year explicit, reducing the chance that a date is interpreted in an unintended year.

Check whether the cells contain real dates

A cell can look like a date while containing text. This often happens when data comes from a CSV file, a web export, or a system that stores dates in a different regional format. Changing the cell’s number format does not convert text into a date; formatting changes how a value appears, not the underlying value that SUMIFS compares.

To check a single cell, assume A2 is the date you want to inspect. This test returns TRUE when Excel recognizes the cell value as a number, which is how a genuine date is stored:

=ISNUMBER(A2)

A FALSE result is a strong sign that the cell contains text or is blank. It is a useful first check, but a TRUE result alone does not confirm that the date is the intended calendar day. Also inspect the displayed value and the source data. A date parsed as the wrong month and day can still be a valid numeric date.

For text that Excel can interpret under your regional settings, you can convert an individual value with DATEVALUE. Assume A2 contains text such as 1/15/2025. The formula below returns the corresponding Excel date serial; format the result as a date if you want to view it as a calendar date:

=DATEVALUE(A2)

If conversion fails or interprets dates incorrectly, confirm the source’s date order before converting a whole column. In Microsoft 365 for Windows, Data > Text to Columns can sometimes convert consistently formatted text dates; check the preview and select the appropriate date order when the wizard offers that option. Another safe option is to import the source again and specify its date format. Do not assume that applying a date format alone has repaired text values.

Handle times on the ending date

Excel date-time values combine a whole-number date with a fractional part for the time. Midnight is the start of the date; 3:30 p.m. is later in that same day. A criterion that says “less than or equal to January 31, 2025” compares against midnight at the beginning of January 31. Consequently, it can exclude January 31 entries that include a time.

For an inclusive calendar range, set the upper limit to the start of the day after the end date and use “less than.” If dates are in A2:A6, amounts in C2:C6, and E2 and F2 contain date-only start and end values, this formula includes all times on F2:

=SUMIFS($C$2:$C$6,$A$2:$A$6,">="&E2,$A$2:$A$6,"<"&F2+1)

This boundary method is useful for daily sales, appointments, and imported transaction logs. It also avoids trying to specify the final possible time value, which can be awkward when the data’s precision varies. Make sure F2 is the intended calendar date, not a date-time value whose time component changes what “the following day” means in your setup.

Common reasons the result is zero or too small

The criteria are typed as one text string. Use an operator in quotes joined to a cell value, for example ">="&E2. Do not put the cell reference inside the quoted text.
The dates are text. Convert them to actual date values, or correct the import process. A date-looking display is not proof of a numeric date.
The end-date condition excludes times. Replace an inclusive comparison to the end date with a “less than next day” boundary when timestamps may be present.
The start date is after the end date. Check the two input cells and confirm they contain the expected year, month, and day.
The ranges do not line up. The amount range and each date criteria range should cover corresponding records and have matching dimensions. Avoid including a header in one range but not the others.
The wrong date column is referenced. A workbook may have separate order, invoice, and payment dates. Verify that the criteria range uses the date relevant to the question.
Other criteria are filtering records out. Every condition in SUMIFS must be true for a row to contribute. Temporarily check the date conditions alone, then add other conditions back one at a time.
The sum values are not numeric. Amounts imported as text may not add as expected. Check the amount column as well as the date column.

A practical troubleshooting sequence

Work from the inputs toward the formula rather than changing several things at once. This makes it easier to identify whether the problem is data, boundaries, or another criterion.

Click the start and end cells and confirm their displayed dates and years. Check that the start is not later than the end.
Test a date cell with ISNUMBER. If the result is FALSE, resolve the text-date issue before relying on date comparisons.
Inspect a date that should be included on the final day. If it has a time, use the exclusive next-day boundary.
Temporarily remove unrelated criteria and verify that the date-only version returns the expected total.
Compare the sum range and date range row by row. Confirm that no header, blank row, or shifted reference changes which amount belongs to which date.
Once the basic result is right, restore the other conditions and check each one against a record you expect to qualify.

When formulas are copied down or across, absolute references can help keep the data ranges fixed while input cells move as intended. The dollar signs in $A$2:$A$6 and $C$2:$C$6 lock those ranges. They do not change how date criteria work, but they can prevent a copied formula from gradually pointing at the wrong rows.

Use date cells rather than ambiguous typed dates

Entering criteria such as ">=1/1/2025" directly into a formula is risky: Excel may interpret the slashes according to regional settings, or treat the content differently than intended. Referencing cells containing real dates is easier to audit and lets users change the period without editing the formula. For fixed dates, DATE(year,month,day) makes the intended components clear.

When building a monthly report, it can be simpler to define the first day of the month and the first day of the following month, then use a less-than comparison for the upper boundary. This pattern works regardless of whether individual transactions have times. If the report period is selected by a user, label the input cells clearly and use actual dates rather than text labels such as “January.”

SUMIFS compared with other approaches

SUMIFS is usually the clearest choice when the goal is to total a numeric column subject to several conditions. If the task is simply to count matching dates, COUNTIFS follows similar criteria logic but counts qualifying records instead of summing amounts. If you need to inspect or return individual matching rows rather than a total, filtering the table may be more useful than changing the sum formula.

A PivotTable is another option for recurring summaries by month or date. It can group date fields and summarize amounts, but it depends on the source dates being recognized correctly and may need refreshing after source data changes. For a single total, a well-formed SUMIFS formula is often more direct. Neither a PivotTable nor a different function will fix dates that were imported as text; correcting the underlying data remains important.

Frequently Asked Questions

Why does SUMIFS return zero for dates that are visibly in the range?
First check whether the date cells are real Excel dates with ISNUMBER. Then verify the criteria syntax, the date column, and any additional conditions. A cell that displays a date can still contain text, and one failing condition is enough to exclude a row.

Should I use less than or equal to for the end date?
Use less than or equal to when the date values have no time component and you want to include that exact date. If timestamps may exist, use a less-than test against the day after the end date so afternoon records on the final day are included.

Do I need to format the result cell as a date?
No. The result of a sum is an amount, so format the result as a number, currency, or another suitable numeric format. Format the date inputs and date column as dates for readability, but remember that formatting does not convert text into a real date.

Can I use SUMIFS with dates in a different regional format?
Yes, provided Excel has stored the criteria values as dates and the formula uses the correct cell references. Using date cells or DATE(year,month,day) is safer than typing ambiguous date strings. Check imported data carefully because regional settings can affect how text is interpreted.

Does this formula work in Excel for Mac or Excel for the web?
The SUMIFS formula and date-serial logic are the same in current Excel for Windows, Mac, and the web. The menus used to import or convert text dates may differ by platform, so use the equivalent data-import controls available in that version.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.