How to Use SUMIF or SUMIFS for Either of Two Criteria in Spreadsheets
Question details
The user needs to calculate the sum of values in one column (e.g., column B) when the corresponding cell in another column (e.g., column A) meets either of two specific conditions (e.g., equals 2 or 3).
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Applying an OR logic to a conditional summation formula where a single criteria range can match multiple acceptable values.
- Observed behavior
- By default, SUMIFS applies AND logic across multiple criteria ranges. The user requires a workaround to apply OR logic within the same criteria range to output a single combined sum.
Ensure that your sum range and criteria range are exactly the same size (e.g., B1:B100 and A1:A100) to prevent a #VALUE! error when calculating the formula.
Use SUM and SUMIFS with an Array Constant
This is the most efficient and concise method to sum values based on multiple OR conditions in the same column.
By wrapping a SUMIFS function inside a SUM function and providing an array constant (values inside curly brackets), you force the spreadsheet to evaluate both conditions and add their respective totals together automatically.
Click on the empty cell where you want the final summed total to appear.
Type the formula: =SUM(SUMIFS(B1:B100, A1:A100, {2,3})) into the formula bar. Replace 2 and 3 with your specific criteria, and adjust the cell ranges as needed.
Press Enter. The system will sum the values in column B where column A is 2, sum the values where column A is 3, and add both results together.
Combine Two Separate SUMIFS Formulas
A highly compatible, straightforward approach that works in all older and modern spreadsheet applications by adding two independent formulas.
Use SUM and FILTER Functions (Modern Alternative)
An alternative method for newer spreadsheet versions that filters the dataset based on multiple conditions before summing.
Effortlessly Calculate Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced mathematical functions, array constants, and dynamic arrays. You can easily manage complex OR logic formulas like nested SUM and SUMIFS without compatibility issues.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your data set.
- 2. Select the output cell: Click the specific cell where you want the conditional sum to appear.
- 3. Enter the formula: Type =SUM(SUMIFS(B1:B100, A1:A100, {2,3})) into the formula bar.
- 4. Calculate: Press Enter to instantly process the OR criteria and display your accurate total.
Frequently Asked Questions
Can I use SUMIF instead of SUMIFS for this problem?
Yes, if you only have one criteria range, you can use SUMIF. The formula would look like =SUM(SUMIF(A1:A100, {2,3}, B1:B100)). However, SUMIFS is generally recommended as it allows you to easily add more criteria ranges later if your needs change.
Why does SUMIFS usually apply AND logic instead of OR logic?
By default, SUMIFS evaluates multiple conditions across different ranges by verifying that ALL conditions are met simultaneously (AND logic). To evaluate whether ANY of the conditions are met (OR logic) within the same range, you must use array constants like {value1, value2} wrapped in a SUM function.
How do I sum values for three or more OR criteria?
You can expand the array constant method by simply adding more values inside the curly brackets. For example, to sum values where column A is 2, 3, or 4, use: =SUM(SUMIFS(B1:B100, A1:A100, {2,3,4})).
What if my OR criteria are in two completely different columns?
If you need to sum column C when column A equals 2 OR column B equals 3, the array constant method won't work perfectly without risking double-counting. Instead, you should add two SUMIFS and subtract the overlap: =SUMIFS(C:C, A:A, 2) + SUMIFS(C:C, B:B, 3) - SUMIFS(C:C, A:A, 2, B:B, 3).
