How to SUM Values When Either of Two Conditions is True in Excel
Question details
The user needs to sum values in a column based on an OR logic condition across two other columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a total sum where multiple criteria are evaluated as OR conditions rather than the default AND behavior of SUMIFS.
- Observed behavior
- Since SUMIFS only supports AND logic natively, the user requires alternative formulas like SUMPRODUCT or array formulas to calculate the sum when either column A or column B meets a specific criterion.
Identify the columns containing your criteria and the column containing the values to be summed. Ensure your data ranges are consistent in size across the formula to avoid calculation errors.
Use SUMPRODUCT for Broad Compatibility
The SUMPRODUCT function is the most reliable way to apply OR logic across multiple columns without requiring special keystrokes or newer software versions.
In spreadsheet formulas, adding a plus sign (+) between two logical tests creates an OR condition. SUMPRODUCT evaluates these arrays natively without needing Ctrl+Shift+Enter.
Click the cell where you want the total calculated sum to appear.
Type =SUMPRODUCT((((A2:A11>0)+(B2:B11>0))>0)*C2:C11) and press Enter. This formula checks if the value in column A or column B is greater than 0, and multiplies the TRUE/FALSE result by the corresponding value in column C to get the sum.
Use a SUM(IF) Array Formula
If you prefer traditional IF logic, an array formula can be used to evaluate multiple OR conditions.
Use BYROW and LAMBDA (Microsoft 365 Only)
For users on the latest Microsoft 365 versions, dynamic array functions offer a modern approach to row-by-row logical tests.
Easily Process Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical formulas including SUMPRODUCT and array functions. You can seamlessly apply OR logic to your data calculations within a lightweight, highly compatible interface.
- 1. Download and install: Get WPS Office from the official website, install it, and open the WPS Spreadsheet module.
- 2. Open your workbook: Load your existing Excel file containing the data ranges to be summed.
- 3. Apply the formula: Use the SUMPRODUCT formula just as you would in Excel to instantly calculate your OR logic sums without formatting loss.
Frequently Asked Questions
Why can't I just use SUMIFS for this calculation?
The SUMIFS function is hardcoded to use AND logic, meaning all criteria must be met for a row to be summed. To use OR logic (where only one of the conditions needs to be true), you must use alternative functions like SUMPRODUCT or SUM combined with IF.
What does the plus sign (+) do in the SUMPRODUCT formula?
In spreadsheet array formulas, the plus sign (+) acts as an OR operator. It adds the TRUE/FALSE results of two logical tests together. A result of 1 or greater means at least one condition is true, allowing the formula to include that row in the final sum.
Why is my SUM(IF) formula returning a #VALUE! error?
If you are using an older version of Excel, you must confirm the SUM(IF) array formula by pressing Ctrl+Shift+Enter instead of just pressing Enter. If you do not use this shortcut, the spreadsheet software will not evaluate the arrays correctly and will return a #VALUE! error.
