SUMIFS Between Two Dates in Excel: How-To
This article explains how to sum values that fall between two dates in Excel using the SUMIFS function. You will learn the exact formula structure, how to set up date criteria correctly, and how to avoid common errors that produce wrong totals. The core formula is SUMIFS(sum_range, date_range, ">="&start_date, date_range, "<="&end_date), where the dates can be typed directly, referenced from cells, or calculated with functions like TODAY().
Why SUMIFS Is the Right Tool
Excel offers several ways to sum with conditions, but SUMIFS is the most direct for date ranges. Unlike SUMIF, which handles only one condition, SUMIFS accepts multiple criteria, so you can set both a lower and an upper bound in a single formula. It also handles whole columns efficiently, which makes it ideal for growing data sets. For example, if you have sales in column B and dates in column A, =SUMIFS(B:B, A:A, ">="&DATE(2024,1,1), A:A, "<="&DATE(2024,1,31)) sums all sales in January 2024.
The function reads the criteria as text, so the comparison operators must be enclosed in quotes and joined to the date with an ampersand (&). This is the most common source of confusion, but once you understand it, the formula becomes straightforward.
How the SUMIFS Formula Works
The syntax is: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). For dates between two points, you use the same date column twice: once for the start bound and once for the end bound. The sum range is the column with the values you want to add.
- sum_range – the cells to add (e.g., B2:B100)
- criteria_range1 – the date column (e.g., A2:A100)
- criteria1 – the lower bound, written as
">="&start_date - criteria_range2 – the same date column again
- criteria2 – the upper bound, written as
"<="&end_date
Both criteria are inclusive, meaning the start and end dates themselves are included in the sum. If you want to exclude the endpoints, simply change the operators to ">" and "<".
Using Cell References vs. Hardcoded Dates
Typing dates directly into the formula is possible but not recommended, because Excel may interpret them as text or regional date formats can cause errors. The safest approach is to put the start and end dates in separate cells and reference them. For instance, with D1 as the start date and E1 as the end date, the formula becomes:
=SUMIFS(B2:B100, A2:A100, ">="&D1, A2:A100, "<="&E1)
This lets you change the dates without editing the formula, and it avoids the ambiguity of text dates. If you need the dates to be dynamic, you can use TODAY() inside the formula, such as ">="&TODAY()-30 for the last 30 days, but remember that TODAY() is volatile and recalculates every time the workbook opens.
Common Mistakes and How to Fix Them
Several errors frequently trip up users when summing between dates:
- Using text dates without quotes –
SUMIFS(B:B, A:A, ">=2024-01-01")treats the date as text and returns 0. Always useDATE()or cell references. - Mixing date formats – If your dates are stored as text (e.g., "Jan 1, 2024"), SUMIFS will not recognize them. Check that the date column is formatted as Date, not Text.
- Reversing the operators – Using
"<="for the start and">="for the end will produce a wrong or zero result. The lower bound must use">="or">". - Whole-column references with other data – If you use A:A and B:B, but row 1 contains headers, SUMIFS ignores them because they are text, but if you have any stray numbers in the date column, they might be included. It is safer to use a defined range like A2:A1000.
- Using SUMIF instead of SUMIFS – SUMIF cannot handle two criteria on the same range without array tricks. Stick to SUMIFS.
Alternatives: SUMPRODUCT and Pivot Tables
If you need more flexibility, SUMPRODUCT can sum between dates without the ampersand syntax. The formula =SUMPRODUCT((A2:A100>=D1)*(A2:A100<=E1)*B2:B100) works, but it is less intuitive and can be slower on large datasets. A Pivot Table is a better choice when you want to group by month or quarter, because you can drag the date field into the Rows area and group it, then add the value field to the Values area. This requires no formulas at all and is often easier for recurring reports.
For a one-off calculation, SUMIFS is the fastest and clearest. For interactive dashboards, consider using a slicer on a Pivot Table to filter dates visually.
Handling Dates in Different Time Zones or Text Formats
Excel stores dates as serial numbers, so time zones do not affect the comparison as long as the values are true dates. However, if you import data from another system, dates may come in as text strings like "2024-01-01 14:30". In that case, you need to convert them using DATEVALUE() or the Text-to-Columns feature before using SUMIFS. A quick test is to check if the cell is left-aligned (text) or right-aligned (date). If they are text, use =DATEVALUE(A2) in a helper column and then apply SUMIFS to that helper column.
Frequently Asked Questions
How do I sum between two dates including the start and end dates?
Use the operators ">=" and "<=" as shown above. For example, =SUMIFS(C:C, A:A, ">="&D1, A:A, "<="&E1) includes both D1 and E1 in the sum.
Why does my SUMIFS return 0 even though I have matching dates?
Most likely your dates are stored as text. Check the cell alignment: text is left-aligned, dates are right-aligned. Convert text to dates using DATEVALUE or Text-to-Columns, then retry.
Can I use SUMIFS with dates in the criteria as text like "2024-01-01"?
Yes, but you must enclose the date in quotes and ensure the format matches your system's date format. It is safer to use DATE(2024,1,1) or a cell reference to avoid regional format issues.
How do I sum values for the last 30 days dynamically?
Use =SUMIFS(B:B, A:A, ">="&TODAY()-30, A:A, "<="&TODAY()). This updates automatically each day.
What if my date range spans multiple years?
SUMIFS handles any date range as long as the dates are true Excel dates. Just set the start and end dates correctly; the formula will sum all values in that span.
Is there a way to sum between two dates without using SUMIFS?
Yes, you can use SUMPRODUCT, a Pivot Table, or even an array formula with SUM and IF. However, SUMIFS is the simplest and most efficient for this specific task.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.