How to Sum Values When Either of Two Columns Matches a Criterion in Excel
Question details
The user needs a spreadsheet formula to sum positive values in a specific column if either of two other columns contains a specific text code (implementing OR logic).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Financial tracking or data analysis requiring conditional sums based on multiple columns with OR logic, while explicitly excluding zero and negative amounts.
- Observed behavior
- Standard SUMIF and SUMIFS functions natively use AND logic for multiple criteria, making it challenging to sum values based on an OR condition across two different columns.
Ensure your data ranges are identical in size across all referenced columns to avoid formula calculation errors, and verify that your numerical column does not contain text formatted as numbers.
Use the SUM and FILTER Functions (Modern Approach)
This is the most efficient method for modern spreadsheet versions supporting dynamic arrays, as it handles complex AND/OR logic cleanly in a single formula.
By nesting the FILTER function inside the SUM function, you can use mathematical operators to create logical tests. The asterisk (*) acts as the AND operator, while the plus sign (+) acts as the OR operator.
Click on the cell where you want the total calculated sum to appear.
Type the formula: =SUM(FILTER('List of Transactions'!G3:G373,('List of Transactions'!G3:G373>0)*(('List of Transactions'!K3:K373="R")+('List of Transactions'!I3:I373="R"))))
Press Enter. The formula will filter rows where column G is greater than 0 AND either column K OR column I contains 'R', and then sum the resulting values.
Combine Multiple SUMIFS Functions (Traditional Approach)
This is the best alternative if you are using an older version of Excel that does not support the dynamic FILTER function.
Easily Handle Advanced Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports modern array functions like SUM and FILTER, making it simple to process complex conditions and OR logic in your data. It provides a seamless alternative to Excel with complete formula compatibility.
- 1. Open your workbook: Launch WPS Spreadsheet and open your financial or transaction data file.
- 2. Convert to Table: Select your data range and go to Insert > Table so your formulas auto-expand.
- 3. Apply the formula: Type your =SUM(FILTER(...)) formula into the target cell.
- 4. Calculate instantly: Press Enter to instantly view your conditionally summed data without needing complex array keystrokes.
Frequently Asked Questions
Why doesn't the standard SUMIF function work for OR logic?
The SUMIF and SUMIFS functions are designed to use AND logic when evaluating multiple criteria ranges. To evaluate OR logic across different columns, you must either combine multiple SUMIFS formulas with addition/subtraction or use array-capable functions like SUM with FILTER.
What does the plus sign (+) mean inside the FILTER function criteria?
In array formulas and the FILTER function, the plus sign (+) operates as a logical OR operator. It evaluates if the condition on the left side of the plus sign OR the condition on the right side is true.
How do I ensure my formula updates when I add new rows?
Convert your data into an Excel Table by selecting your data range and pressing Ctrl+T. When you reference table columns in your formula (e.g., Table1[Column1]), the ranges will automatically expand as new transactions are added to the bottom.
