How to Use SUMIFS with Date Criteria in Excel
Question details
The user wants to sum values based on a date criterion stored in another cell, specifically targeting dates before or equal to a certain cell reference.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Calculating conditional sums based on dynamic date ranges using the SUMIFS function.
- Observed behavior
- Directly typing a cell reference with an operator like <D2 as a criterion fails to read the cell's date value correctly, resulting in incorrect calculations.
Ensure that your criteria dates and the dates in your evaluation range are formatted as actual date values in the spreadsheet, rather than plain text.
Concatenate the Comparison Operator with the Cell Reference
By joining the operator and the cell reference with an ampersand (&), the function correctly interprets the date value in the referenced cell.
When using cell references in functions like SUMIFS, placing operators and cell references together inside quotes (e.g., "<D2") causes the function to treat the entire string as text instead of a dynamic reference. To evaluate the cell's actual date, you must enclose the operator in quotes and concatenate it with the cell reference using the & symbol.
Click on the cell where you want the totaled sum to appear.
Type =SUMIFS( followed by your sum_range and date_range arguments, separated by commas.
For the criterion argument, type "<"&D2 to sum values strictly before the date in cell D2, or "<="&D2 to include the date in D2 as well.
Close the parenthesis and press Enter. A complete formula will look like this: =SUMIFS(B2:B10, A2:A10, "<"&D2).
Master Complex Formulas with WPS Spreadsheet
WPS Office provides a highly capable Spreadsheet tool that makes handling advanced functions like SUMIFS, VLOOKUP, and complex date calculations effortless while keeping your workflow smooth.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your date data.
- 2. Access the Function Wizard: Click on the Formulas tab in the top ribbon and select 'Insert Function' to search for SUMIFS.
- 3. Input the arguments: Follow the built-in wizard prompts to enter your Sum_range and Criteria_range safely without manually typing commas.
- 4. Apply dynamic criteria: In the Criteria field, enter your concatenated string like "<"&D2, and press OK to calculate the exact sum instantly.
Frequently Asked Questions
Why does my SUMIFS formula return 0 despite having matching dates?
This typically occurs if the dates in your criteria range are formatted as text instead of valid Excel dates, or if the comparison operator is incorrectly grouped inside quotes (e.g., "<D2" instead of "<"&D2). Select your date column and use the 'Text to Columns' feature to convert text to dates.
Can I use SUMIFS to sum values between two specific dates?
Yes. You can input multiple criteria ranges and criteria within the same SUMIFS formula. To sum values between dates in D2 and E2, your formula would look like: =SUMIFS(sum_range, date_range, ">="&D2, date_range, "<="&E2).
How do I hardcode a specific date text into the SUMIFS criteria instead of a cell reference?
To use a hardcoded date, enclose the operator and the date together in quotation marks, such as "<1/1/2024". Alternatively, use the DATE function to avoid regional format errors, writing the criterion as "<"&DATE(2024,1,1).
