How to Sum Values When Each Date Is Before or Equal to Another Date in WPS Office Spreadsheet
Question details
The user needs a formula that sums a numeric column only for rows where the status is Closed and the closure date is less than or equal to the row-specific target date, because SUMIFS cannot compare two criteria ranges row by row.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has a table with value, status, closure date, and target date columns and needs a row-wise conditional sum.
- Observed behavior
- SUMIFS cannot directly compare each row's closure date to the matching target date, so an array-based SUMPRODUCT formula is required.
Confirm that the status column contains the exact text "Closed", the date columns contain valid dates, and all formula ranges cover the same number of rows.
Use SUMPRODUCT to sum values with a row-by-row status and date condition
Use this approach when SUMIFS cannot evaluate a closure date against a matching target date on the same row.
SUMPRODUCT evaluates arrays row by row and can handle two equally sized date ranges in one condition.
The double unary operator -- converts TRUE/FALSE results from the status and date comparisons into 1/0 values so they can be multiplied with the amount column.
Click the cell where you want the conditional sum to appear, such as E1.
Type =SUMPRODUCT(A2:A7,--(B2:B7="Closed"),--(C2:C7<=D2:D7)) and press Enter.
Verify that the result includes only rows with status Closed and a closure date on or before the matching target date.
Sum Row-Specific Date and Status Conditions with SUMPRODUCT in WPS Office Spreadsheet
Open your workbook in WPS Office Spreadsheet and use a SUMPRODUCT array formula to sum values only when each row is Closed and its closure date is on or before the matching target date.
- 1. Open your workbook in WPS Office Spreadsheet: Launch WPS Office, click Menu > Open, and select the .xlsx or .xls file that contains your data.
- 2. Select the result cell: Click the cell where you want the conditional sum result to appear, such as E1.
- 3. Enter the SUMPRODUCT formula: Type =SUMPRODUCT(A2:A7,--(B2:B7="Closed"),--(C2:C7<=D2:D7)) and press Enter.
- 4. Confirm the result: Check that the total matches only the rows with status Closed and a closure date on or before the target date.
Frequently Asked Questions
Why can't I use SUMIFS to compare each closure date with its matching target date?
SUMIFS compares criteria ranges to specific criteria values or separate criteria, but it does not perform a row-by-row comparison between two equal-sized date ranges. Use SUMPRODUCT to evaluate both conditions for each row.
What does -- mean in the SUMPRODUCT formula?
The double unary operator -- converts TRUE and FALSE results from each condition into 1 and 0, so SUMPRODUCT can multiply and sum the arrays.
Can I use this SUMPRODUCT formula in WPS Office Spreadsheet?
Yes, WPS Office Spreadsheet supports SUMPRODUCT and array-style calculations. You can enter the same formula directly into any cell.
Do all ranges in the SUMPRODUCT formula need to be the same size?
Yes, the amount range, status range, closure date range, and target date range must contain the same number of rows. Otherwise the formula returns an error or an unexpected result.
