SUMIFS Not Working With Dates? Fix Common Date Criteria

Yes—SUMIFS can sum values by date, but the criteria must match Excel’s underlying date values, not just the way dates look on screen. With actual dates in A2:A6, amounts in C2:C6, the start of January 2026 in E2, and February 1, 2026, in F2, this formula sums January’s records, including any times on those dates: =SUMIFS(C2:C6,A2:A6,">="&E2,A2:A6,"<"&F2). For example, if the January amounts are 100, 250, 50, and 40, the result is 440.

How SUMIFS evaluates date criteria

Excel stores a genuine date as a number, with the displayed date produced by a number format. A date such as January 1, 2026, therefore has a numeric value internally; formatting that cell as General may reveal a serial number instead of a calendar date. SUMIFS compares those underlying values. It does not compare the visual appearance of a date cell.

SUMIFS also has a specific argument order: the sum range comes first, followed by a criteria range and its criterion, then any additional range-and-criterion pairs. To include a date boundary, construct a criterion by joining an operator to a date value. Typing a criterion like ">=E2" asks Excel to compare against text beginning with E2, not the value in cell E2. The ampersand joins the operator text to the cell value.

In the opening example, E2 is January 1 and F2 is February 1. The first condition includes values on or after the start; the second excludes values on or after February 1. This start-inclusive, next-period-exclusive pattern is reliable for date ranges and avoids ambiguity about what counts as the final moment of a day.

Check whether the cells contain real dates

A cell can look like a date while containing text. This commonly happens when data is copied from a website, exported from another system, imported from a CSV file, or entered in a format Excel does not recognize under the computer’s regional settings. Text such as “2026-01-15” might be recognized in one setup and remain text in another.

Check a sample date cell by changing its number format to General. A genuine date typically displays as a number; text usually remains text. You can also test a cell directly. With the candidate date in A2, enter this formula in another cell: =ISNUMBER(A2). TRUE indicates a numeric value, though it does not by itself prove that the number represents the date you intended. FALSE is a strong sign that the entry is text.

If dates are text, convert them before relying on SUMIFS. For a consistent, recognizable text format, Excel’s DATEVALUE function may be useful. With a date string in A2, this formula returns its date value when Excel can interpret the string: =DATEVALUE(A2). The interpretation of ambiguous strings such as 03/04/2026 can vary by locale: it might mean March 4 or April 3. Confirm the intended order before converting a whole column.

For a column with a consistent layout, another option in Excel for Microsoft 365 on Windows is to select the data and choose Data > Text to Columns, then specify the correct date order in the conversion steps. Review a copy or a few converted values first. On Mac, the Data tab also includes Text to Columns, though the dialog appearance can differ. Changing a cell’s number format alone does not convert text into a real date.

Account for times hidden in date cells

Date-time values are a frequent reason a formula appears to miss records on the last day. Excel stores a time as a fractional part of a day. A cell containing January 31 at 5:30 p.m. is later than the date value for January 31 at midnight, even if the cell is formatted to display only the date. A criterion that says “less than or equal to January 31” can exclude that afternoon entry.

For a single day, use a lower boundary at the day’s midnight and an upper boundary at the next midnight. With date-time values in A2:A100, amounts in C2:C100, and the target date in E2, this formula includes every time on that date: =SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<"&(E2+1)). The upper limit is exclusive, so an entry at 11:59 p.m. is included while one at midnight the following day is not.

The same principle works for weeks, months, and years: include the first instant of the period and exclude the first instant of the next period. For example, with the dates and amounts in the same columns and E2 containing any date in the month, use this formula to sum the entire month: =SUMIFS(C2:C100,A2:A100,">="&DATE(YEAR(E2),MONTH(E2),1),A2:A100,"<"&DATE(YEAR(E2),MONTH(E2)+1,1)). DATE handles the next month boundary even when the current month is December.

Build criteria with operators and cell references

When criteria are typed directly into a formula, operators and dates need to be assembled correctly. A common mistake is putting the operator and a cell reference together inside quotation marks. Another is typing a date into a formula as text and assuming Excel will parse it the same way on every computer. Cell references containing valid dates avoid much of that uncertainty.

With dates in A2:A100, amounts in C2:C100, and the first and last dates in E2 and F2, respectively, this formula includes both boundary dates when the date cells have no times: =SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2). If the source can contain times on F2, use the next day as an exclusive end boundary instead. With the same ranges and F2 holding the final calendar date, use: =SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<"&(F2+1)).

For a fixed date, DATE is clearer and less dependent on regional date-entry conventions than a quoted string such as “1/15/2026.” With dates in A2:A100 and amounts in C2:C100, this formula sums entries on January 15, 2026, if the source has dates without times: =SUMIFS(C2:C100,A2:A100,DATE(2026,1,15)). If those cells may contain times, use a lower and upper boundary for that day rather than exact equality.

Check ranges, blanks, and the sum range

Every criteria range in SUMIFS must correspond in size and shape to the sum range. If amounts are in C2:C100, the date criteria range should ordinarily be A2:A100, not A2:A99 or A:A combined with a differently sized range. Mismatched ranges can produce an error or results that do not reflect the intended row-by-row pairing. Also confirm that the date and amount on each record are on the same row.

Inspect the sum range if the result is zero or unexpectedly small. SUMIFS adds numeric values; amounts stored as text may not contribute as expected. Check that the criteria range points to the date column rather than a nearby text label, and that the formula is not accidentally including a header row. If the result is plausible but short, check for filters or hidden rows only if the formula’s surrounding logic depends on them: SUMIFS itself evaluates qualifying cells in its ranges and does not automatically restrict its calculation to visible rows.

Blank date cells, extra criteria, and copied formulas can also change the outcome. Review every criteria pair, especially if a formula has several conditions such as region, category, and date. Make sure copied references have the intended absolute or relative behavior. For example, a boundary reference that should remain in E2 when copied down may need dollar signs, while a row-specific date reference should usually move with the formula.

Use a reliable month or year boundary

Month-based reports are often more dependable when they use a start date and the first day of the following month, rather than trying to calculate a month’s last date. Months have different lengths, and February varies by year. The exclusive boundary handles those differences without a special case for each month.

For a yearly total, use the first day of the year as an inclusive lower bound and the first day of the next year as an exclusive upper bound. With dates in A2:A100, amounts in C2:C100, and a year number such as 2026 in E2, use: =SUMIFS(C2:C100,A2:A100,">="&DATE(E2,1,1),A2:A100,"<"&DATE(E2+1,1,1)). This includes date-time entries throughout the selected year without requiring a final time-of-day value.

For a recurring monthly report, putting the month’s first day in a cell makes the period visible and easy to review. The next-month boundary can be calculated from that cell, or placed in a second cell and checked separately. A visible boundary is useful when a report is handed to someone else, because it makes the intended period clearer than a long formula containing fixed dates.

Compare SUMIFS with other ways to total dates

SUMIFS is a practical choice when a total depends on one or more criteria, such as a date range plus a department or product. SUMIF can handle a single condition, but SUMIFS is usually easier to extend when a report needs several conditions. For totals based on a date interval, both still depend on valid date values and correctly constructed criteria.

A PivotTable can be more convenient when readers need to explore totals by month, quarter, or year interactively. It is not a cure for dates imported as text: the source field still needs to be recognized as dates for grouping to work as intended. A helper column that extracts a month or normalizes a date may also make a complex report easier to audit, but it adds another field that must be maintained. Choose the method that makes the source data and reporting rules easiest to inspect.

Frequently Asked Questions

Why does SUMIFS return zero for a date that is visibly in the range? The date may be text, the criteria may have the operator and cell reference joined incorrectly, or the source may contain times that place a value outside an exact-date test. Check the underlying value, the formula’s ranges, and whether a start-inclusive, next-day-exclusive boundary is needed.

Can SUMIFS match a date when the cell also contains a time? Yes. Use a lower boundary for the date and an exclusive upper boundary at the following day. That captures every time during the calendar date, unlike an equality test that matches only the date value at midnight.

Does changing the date format fix text dates? No. Number formatting changes how a value is displayed, but it does not turn text into a numeric date. Convert the data using a suitable method, and verify that the resulting cells are numeric dates.

Why does a formula include some days in a month but miss the final day? The final day may contain time values later than midnight. Use the first day of the month as the inclusive start and the first day of the next month as the exclusive end, rather than an inclusive comparison against the final date at midnight.

Can I use a date typed directly into the formula? You can, but a quoted date string may be interpreted differently under different regional settings. A cell reference containing a verified date, or DATE(year,month,day), makes the intended value more explicit.

Why does the same formula work on one computer but not another? Regional settings can affect how text dates are interpreted, and imported data may not have been converted consistently. Check whether the source cells are numeric dates and use explicit date boundaries; do not assume matching display formats mean the underlying values are identical.

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.