To sum values in Excel where the date is before a specific date, use the SUMIFS function with a less-than operator: =SUMIFS(sum_range, date_range, "<"&DATE(2025,1,1)). This returns the total of all cells in sum_range where the corresponding date in date_range is earlier than January 1, 2025. The formula works in Excel for Microsoft 365, Excel 2021, 2019, and earlier versions, and the same syntax applies in Google Sheets.
Why SUMIFS Is the Right Tool for Date Cutoffs
Excel offers several ways to sum by condition, but SUMIFS is the most flexible because it handles multiple criteria and works with dates natively. Unlike SUMIF, which only accepts one condition, SUMIFS lets you combine a date cutoff with other filters, such as a product name or region. Dates in Excel are stored as serial numbers, so comparisons like “before” or “after” are simple numeric operations. The key is to construct the criteria correctly: either use a cell reference with the less-than operator, or embed a DATE function to avoid regional date format issues.
How to Write a SUMIFS Formula with a Date Condition
Assume your data has dates in column A (A2:A100) and sales amounts in column B (B2:B100). To sum all sales before March 1, 2025, enter this formula in any empty cell:
=SUMIFS(B2:B100, A2:A100, "<"&DATE(2025,3,1))
This returns the total of all amounts in B where the date in A is earlier than March 1, 2025. The "<"& concatenates the less-than symbol with the date serial number. If you have a cutoff date in cell D1 (formatted as a date), use:
=SUMIFS(B2:B100, A2:A100, "<"&D1)
Both formulas work identically. For an “on or before” cutoff, change the operator to "<="&. For example, to include March 1 itself, use "<="&DATE(2025,3,1).
Using Cell References vs. DATE() in Criteria
There are two common ways to supply the date in the criteria. The first is a cell reference, which is dynamic—if you change the date in the cell, the formula recalculates. The second is the DATE function, which is static and safe for hard-coded cutoffs. The DATE function takes YEAR, MONTH, and DAY arguments, so DATE(2025,3,1) always returns March 1, 2025, regardless of your system’s date format. Avoid typing dates directly as text like "3/1/2025" because Excel may interpret it differently depending on locale. Always use the DATE function or a cell reference to ensure consistency.
Common Mistakes and How to Avoid Them
One frequent error is forgetting the ampersand (&) when combining the operator with a cell reference. Writing "<"D1 is invalid; you need "<"&D1. Another issue is using the wrong comparison operator—"<" means strictly before, while "<=" includes the cutoff date. Also, ensure the date column contains real dates, not text. If your dates are stored as text, the comparison may fail. To check, select the date column and look at the number format—if it shows a number like 45288, it’s a real date. If it shows a string like “Jan 1, 2025”, it may be text. Use the DATEVALUE function to convert text dates if needed.
Extending SUMIFS with Multiple Criteria
The real power of SUMIFS appears when you add more conditions. For instance, to sum sales before March 1, 2025, for a specific product in column C (say “Widget”), use:
=SUMIFS(B2:B100, A2:A100, "<"&DATE(2025,3,1), C2:C100, "Widget")
You can add up to 127 pairs of range/criteria in modern Excel. Each additional pair narrows the sum further. The order of pairs doesn’t matter, but each range must have the same number of rows and columns as the sum range. This makes SUMIFS ideal for dashboards where you filter by date, category, and region simultaneously.
Alternatives: SUMPRODUCT and Filtering
If you need to sum before a date and your Excel version is older (pre-2007), SUMIFS won’t work—use SUMPRODUCT instead. For example:
=SUMPRODUCT((A2:A100<DATE(2025,3,1))*B2:B100)
This multiplies a TRUE/FALSE array by the amounts, effectively summing only where the condition is TRUE. Another alternative is to use Excel’s Table filtering: select your data, press Ctrl+Shift+L to add filter arrows, then filter the date column to “Date Filters” > “Before…” and read the sum from the status bar. However, this is manual and not formula-driven. For dynamic arrays in Excel 365, you could use FILTER and SUM, but SUMIFS is simpler and more efficient for a single total.
Frequently Asked Questions
How do I sum if date is before today? Use =SUMIFS(B2:B100, A2:A100, "<"&TODAY()). The TODAY function returns the current date, and the formula sums all amounts with dates before today. This updates automatically each day.
Why does my SUMIFS return 0 even though there are matching dates? Most likely your date column contains text or the criteria is incorrectly constructed. Check that the date cells are formatted as dates and that you used the ampersand correctly. Also, ensure your sum range and criteria range have the same size.
Can I use SUMIFS with a date range like between two dates? Yes, add two criteria pairs: =SUMIFS(B2:B100, A2:A100, ">="&DATE(2025,1,1), A2:A100, "<"&DATE(2025,3,1)). This sums all dates in January and February 2025.
Does SUMIFS work in Google Sheets? Yes, the same formula syntax works in Google Sheets. The only difference is that Google Sheets may automatically convert dates, but the DATE function and ampersand method are identical.
What if my cutoff date is in a cell with a time component? If the cell contains a date and time (e.g., 3/1/2025 12:00 PM), the comparison will include that exact moment. To ignore time, use "<"&DATE(YEAR(D1),MONTH(D1),DAY(D1)) or ensure the cell has only a date.
Related
- Excel Power Query Web Data: How to Import & Refresh
- Excel Power Query Weighted Average: How to Calculate
- Excel Power Query Undo: What Works and What Doesn’t
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- LEFT in Excel: Extract Text from the StartIn Excel, use the LEFT function to return characters from the beginning of a text value: with text…
- Excel Formula for Weeks Between Two Dates: Full WeeksTo calculate weeks between two dates in Excel, subtract the start date from the end date and divide…
- Divide a Cell by a Number in Excel: Formula GuideTo divide a cell by a number in Excel, enter a formula such as =A2/5 in an empty…
- Filter in Excel With Blank Rows: Keep Records TogetherTo filter data that contains blank rows, select the entire data range—including the blank rows—and choose Data >…
- Power BI DAX Concatenate: Examples and UsageIn Power BI DAX, join two text values with the ampersand operator (&) or the CONCATENATE function; to…
- C++ String Functions: What They Do and How to Use ThemIn C++, string functions are operations such as size(), find(), substr(), and replace() on std::string. If you mean…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.