SUMIFS Between Dates Not Working? Common Fixes

If your dates are in A2:A100, amounts are in B2:B100, and the start and end dates are in D2 and E2, use =SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2) for an inclusive date range. When that returns zero or the wrong total, the usual causes are dates stored as text, timestamps hidden behind date formatting, criteria written without the required concatenation, or ranges that do not match in size.

How SUMIFS date criteria work

SUMIFS adds values from a sum range only when every condition in its criteria ranges is met. Its argument order is the sum range first, followed by one or more criteria-range and criterion pairs. To define a date window, Excel needs two conditions on the date column: a date on or after the start, and a date on or before the end. Both conditions can use the same date range.

A criterion such as greater than or equal to a date combines an operator and a value. When the date is in a cell, join the operator text to the cell reference with an ampersand. In the example above, ">="&D2 means “on or after the value in D2.” The second condition uses the same pattern for the end date. Without the ampersand, Excel may treat the criterion as literal text rather than an operator followed by a cell value.

For a small illustrative data set, suppose A2:A5 contains January 1, January 15, January 31, and February 1, while B2:B5 contains 10, 25, 15, and 30. With D2 set to January 1 and E2 set to January 31, the formula returns 50: it includes 10, 25, and 15, but excludes 30. The dates and amounts must be in the corresponding rows for the result to mean what you intend.

Check the formula structure first

Before troubleshooting the dates themselves, check that the formula uses the correct argument order and paired ranges. The sum range is where the numbers to add live; each criteria range is where Excel checks a condition. A common mistake is to put the date range first, or to provide a criterion without its matching range.

For example, with dates in A2:A100, amounts in B2:B100, and bounds in D2 and E2, the structure is:

Sum range: B2:B100.
First criteria range and criterion: A2:A100 and greater than or equal to D2.
Second criteria range and criterion: A2:A100 and less than or equal to E2.

All ranges used by SUMIFS must have the same dimensions and shape. A sum range of B2:B100 paired with a criteria range of A2:A99 can produce a range-size error rather than a useful total. Selecting whole columns consistently, such as B:B and A:A, is also possible, though bounded ranges or an Excel Table are often easier to audit in a large workbook.

Make sure the cells contain real Excel dates

Excel stores a genuine date as a number, displayed using a date format. A cell that looks like January 15 may instead contain text such as “15/01/2025.” Text does not behave like the underlying date serial number in a date comparison, so SUMIFS can skip it even when the display looks right. Changing the cell’s number format to Date does not convert text into a date; it only changes how numeric values are displayed.

To check a date column, select a cell and inspect the formula bar and number format. You can also count numeric entries. With dates in A2:A100, this check returns the number of numeric cells in that range; if all 99 cells contain real dates, it returns 99:

=COUNT(A2:A100)

A count lower than the number of filled date cells suggests that some entries may be text, blank, or another nonnumeric value. Be aware that COUNT does not prove every counted number is a valid date: ordinary numbers are numeric too. For mixed or imported data, inspect representative cells and verify the source format. Dates like 03/04/2025 are especially risky because the text could mean March 4 or April 3 depending on the source’s date convention.

If dates were imported as text, convert them using a method that respects their original order and locale. Excel’s Data > Text to Columns wizard can convert suitable date text when you choose the matching date order on the conversion step. For example, a day-month-year source should not be interpreted as month-day-year. For ambiguous dates, confirm the intended meaning with the source rather than guessing. After conversion, check that the resulting cells behave as numeric dates.

Handle times hidden in date cells

A cell formatted to show only a date can still contain a time. Excel stores a date and time as a serial value with the time represented as a fraction of a day. Thus, a value displayed as January 31 may actually be January 31 at 2:30 p.m. A criterion of less than or equal to January 31 at midnight includes the start of that day but excludes later times on January 31.

For data that includes times, a reliable way to include the entire end date is to use an exclusive upper bound: less than the day after the selected end date. With timestamps in A2:A100, amounts in B2:B100, and the first and last calendar dates in D2 and E2, use:

=SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&(E2+1))

If the January 31 rows contain 12 at 9:00 a.m. and 8 at 4:00 p.m., and D2 and E2 are both January 31, this formula returns 20. It includes times throughout the selected day but excludes midnight at the start of February 1. This approach also works when the end date is later than the start date. Do not use the exclusive-next-day version if your intention is to exclude timestamps later on the end date.

Enter criteria correctly and avoid date-text traps

When a criterion uses a comparison operator with a value in a cell, the operator goes inside quotation marks and is joined to the cell reference with &. Writing the operator and a cell address together as plain text will not make Excel substitute the cell’s date. Also check that your start and end cells are not blank, and that the start date is not later than the end date.

For a fixed date window, DATE can construct the date from year, month, and day components. With dates in A2:A100 and amounts in B2:B100, this formula sums dates from January 1 through January 31, 2025, inclusive:

=SUMIFS(B2:B100,A2:A100,">="&DATE(2025,1,1),A2:A100,"<="&DATE(2025,1,31))

If the illustrative data has values of 10, 25, and 15 on those three January dates and 30 on February 1, the result is 50. DATE avoids relying on ambiguous date text typed directly into a formula. It also makes a fixed period explicit, although cell references are generally more convenient when users need to change the reporting dates.

Look for mismatched ranges and unexpected data

A correct date condition cannot fix a formula that points at the wrong rows. Confirm that amounts and dates align row by row, and that the criteria range covers every relevant record. If the first data row is row 2, using a range that starts at row 3 silently leaves out the first record. Check for filters or hidden rows too: SUMIFS evaluates matching cells whether their rows are visible or hidden, so hiding a row does not remove it from the calculation.

Other data issues can make a correct-looking formula seem wrong:

Amounts stored as text may not be added as expected. Check whether the values are numeric, especially after importing CSV files.
Blank date cells do not fall within an ordinary date window, but a mistaken zero or placeholder date may produce an unexpected match for an early period.
Start and end cells may contain dates from the wrong year, or the workbook may use a different date convention than the source.
Duplicate rows are counted separately. SUMIFS does not identify duplicates or deduplicate transactions.
Other criteria in the formula are combined with the date criteria. Every condition must be true for a row to contribute.

To isolate the issue, temporarily test a short range with a few known rows, then compare its expected total with the result. Check the formula in the formula bar and use Formulas > Evaluate Formula in Excel for Windows to follow how Excel evaluates it. When the data is in an Excel Table, structured references can make formulas easier to read, but the date criteria still need the same operator-and-value construction.

Choose inclusive or exclusive date boundaries

“Between these dates” usually means to include both the starting date and the ending date, which is why the ordinary formula uses greater than or equal to and less than or equal to. A reporting interval may instead be defined as a start date inclusive and an end date exclusive. That convention is common when one period ends exactly where the next begins, because it avoids counting a boundary timestamp twice.

For instance, a January period can be defined as on or after January 1 and before February 1. With dates in A2:A100, amounts in B2:B100, and the period start in D2, the upper bound can be constructed as the first day of the following month:

=SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&DATE(2025,2,1))

For this fixed January 2025 example, the formula returns 50 for the illustrative January values 10, 25, and 15, and excludes the February 1 value of 30. For timestamps, this boundary includes all times on January 31 without needing to compare against the last possible time of the day. Use the boundary convention that matches the report’s definition, and apply it consistently across adjacent periods.

Excel version and regional settings

SUMIFS is available in modern desktop Excel, including Microsoft 365 for Windows. The formulas here use commas as argument separators, as required for many English-language installations. Some regional settings use semicolons instead; if Excel rejects a correctly structured formula at the separators, use the separator configured for that installation. The criteria logic remains the same.

The formula works in Excel for Mac as well as Windows. Ribbon locations and some interface details can differ by platform or update, but the core SUMIFS syntax and date serial behavior are the same. In Excel for the web, the same formula logic applies. If a workbook is shared across users with different regional settings, avoid ambiguous date text and consider using genuine dates in cells or DATE(year,month,day) in fixed criteria.

Frequently Asked Questions

Why does SUMIFS return zero between two dates?
Check that the date cells contain real Excel dates, that the start and end references are correct, and that the criteria use concatenation such as ">="&D2. Also confirm that matching rows contain numeric amounts and that all criteria ranges cover the same rows as the sum range.

Does SUMIFS include the end date?
Yes, when the upper criterion is less than or equal to the end-date cell and the data contains dates without times. If the cells contain timestamps, use a less-than criterion for the day after the end date to include the entire final day.

Why is the final day missing even though it looks like a date?
The cell may contain a time that is hidden by its number format. A criterion of less than or equal to the end date usually compares against midnight at the start of that date, so later times fail the test. Use the exclusive next-day boundary when the full day should count.

Can I use SUMIFS with dates stored as text?
Not reliably as date comparisons. Convert the text to actual dates using a method that matches the source’s date order, then verify the conversion. Merely applying a date number format does not convert text to a date.

Can SUMIFS use the same date column for both boundaries?
Yes. Repeat the date range once for the lower-bound condition and again for the upper-bound condition. Each criteria range must match the sum range in size and shape.

A quick troubleshooting order

Start with the simplest checks rather than rebuilding the workbook. Confirm the intended dates in the start and end cells, verify that the date column contains numeric dates, then inspect the formula’s range boundaries and operator syntax. If the data has times, change the upper boundary to the day after the selected end date and make it exclusive. Finally, compare a few known rows with the total and account for any additional criteria. In most cases, this sequence reveals whether the problem is a formula construction issue, a data-type issue, or simply a mismatch between the reporting period and the boundaries the formula is using.

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.