How to Use SUMIFS with Multiple Conditions and a 90-Day Date Limit
Question details
The user needs to calculate a total quantity using multiple text criteria and a dynamic date condition where the target date is no later than 90 days after a specific launch date.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Performing conditional data aggregation requiring both specific text matches (item code, approval status) and a rolling time window.
- Observed behavior
- Looking for the correct syntax to combine multiple string criteria and a date addition logic (+90 days or 3 months) inside a SUMIFS or SUMPRODUCT formula.
Ensure all your date columns are properly formatted as 'Date' rather than 'Text' to allow mathematical operations like adding 90 days.
Use SUMIFS to Calculate Totals Within a 90-Day Window
The SUMIFS function is the most efficient way to sum values based on multiple text criteria and a dynamic date limit.
To filter for dates that are within 90 days of a specific launch date, you must concatenate the comparison operator with the date calculation using the ampersand (&) symbol.
Click on the cell where you want the final calculated total quantity to appear.
Type the formula: =SUMIFS(E2:E10, A2:A10, "123455", C2:C10, "Incremental", F2:F10, "Y", D2:D10, "<="&B2+90)
Ensure the ranges correspond to your columns (e.g., E for quantity, A for item code, C for request type, F for approval, D for ETA, and B2 for the launch date).
Press Enter. The syntax "<="&B2+90 evaluates the launch date, adds 90 days, and includes any rows where the ETA is less than or equal to that resulting date.
Use SUMPRODUCT for Array-Based Conditional Summing
SUMPRODUCT is a versatile alternative that handles array calculations without needing special concatenation syntax for logical operators.
Calculate Totals for a Before-and-After Time Window
If your rules allow a window spanning 90 days before and 90 days after the launch date, you can append an additional lower-bound date condition.
Easily Handle Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides robust, highly optimized support for advanced functions like SUMIFS, SUMPRODUCT, and EDATE, making multi-condition data analysis efficient and seamless.
- 1. Open your dataset: Launch WPS Spreadsheet and open your data file.
- 2. Access the formula tools: Click on the 'Formulas' tab and select 'Insert Function' if you need step-by-step help structuring your query.
- 3. Enter your conditions: Type your SUMIFS or SUMPRODUCT formula into the formula bar.
- 4. Analyze your data: Press Enter to instantly analyze your multi-condition data without performance drops.
Frequently Asked Questions
Why is my SUMIFS formula returning 0 when using date conditions?
This usually happens if the date columns are stored as text instead of numerical dates. Ensure both the launch date and the ETA dates are formatted as 'Date'. Also, verify that you concatenated the operator properly with quotation marks and an ampersand, such as "<="&B2+90.
Can I reference a cell for the 90 days limit instead of typing it directly?
Yes. If you have the number 90 stored in cell H1, you can update your condition to "<="&B2+H1. Remember to lock the cell reference (e.g., "<="&B2+$H$1) if you plan to drag the formula down across multiple rows.
What is the difference between B2+90 and EDATE(B2,3)?
B2+90 adds exactly 90 calendar days to the launch date. EDATE(B2,3) adds exactly 3 calendar months, meaning it accounts for varying month lengths (like 28 days in February vs 31 days in March) so the target date lands on the exact same day of the month.
Does SUMPRODUCT perform better than SUMIFS for multiple criteria?
No, SUMIFS is highly optimized internally and generally calculates faster than SUMPRODUCT for standard multi-condition summing, especially on large datasets. However, SUMPRODUCT is necessary if you need to manipulate arrays inside the formula (like extracting the year from a date) before summing them.
