How to Sum Values with Multiple OR Criteria Using SUMIF in Excel

Achieving OR Logic in Conditional Summation

To successfully combine conditional summing with the OR logical operator, specialized formulas must be constructed. These formulas utilize array constants and functional chaining to process multiple criteria sequentially and then aggregate the results. This approach ensures that any record meeting at least one of the specified conditions is included in the final sum.

The two most powerful and commonly employed techniques for integrating OR logic into conditional summing involve either utilizing the array-processing capabilities of the SUMIFS function (2) or chaining standard SUMIF (5) statements. The choice between these methods depends fundamentally on whether the OR criteria applies to a single column or across several different columns.

Below, we detail the structural differences and appropriate uses for each methodology, starting with the array-based approach which is highly optimized for checking one column against multiple possible values.

Method 1: SUMIFS with OR using Array Constants (One Criteria Column)

This method is the cleanest and most efficient way to perform an OR calculation when all potential criteria (e.g., “Value1,” “Value2,” “Value3”) are being checked against a single criteria range. Instead of manually writing three separate `SUMIF` functions, we pass the criteria as a vertical or horizontal array constant.

When the SUMIFS function (3) encounters an array constant in its criteria argument, it does not return an error. Instead, it internally calculates a resulting sum for each element within that array. For our example array {“Value1″,”Value2”, “Value3”}, SUMIFS (4) executes three separate summations.

The outer SUM function is crucial here. It takes the array of intermediate sums generated by SUMIFS (5) and collapses them into a single, grand total. This final sum represents all records that match “Value1” OR “Value2” OR “Value3.”

The structure requires careful placement of the arguments:

=SUM(SUMIFS(B2:B13, A2:A13,{"Value1","Value2", "Value3"}))

In this setup, the formula first calculates the sum of values in the sum_range B2:B13 where the corresponding value in the criteria_range A2:A13 contains “Value1”, then calculates the sum for “Value2”, and finally for “Value3.” The outermost SUM function (2) then adds these three subtotals together.

Method 2: SUMIF with OR by Addition (Multiple Criteria Columns)

When the OR condition involves checking criteria across different columns—for example, summing records if Column A equals “X” OR Column B equals “Y”—the array constant method (Method 1) is not viable. Instead, the most straightforward approach is to calculate the sum for each condition independently using dedicated SUMIF statements and then add the results together.

This technique is fundamentally different from the array approach because it processes each conditional check separately before aggregation. The results of the first condition are fully calculated, and then the results of the second condition are calculated, and these two distinct totals are merged using the addition operator (+).

It is important to note a potential limitation of this additive method: if a single row meets both conditional checks (e.g., Column A = “X” AND Column B = “Y”), the value in the sum range for that row will be counted twice—once by the first SUMIF and once by the second. If double-counting overlapping records is undesirable, more complex formulas involving the exclusion of the intersection (using AND logic) would be required. However, for a simple OR across columns, the additive approach is standard.

The structure for this multiple-column OR application looks like this:

=SUMIF(A2:A13,"Value1", C2:C13)+SUMIF(B2:B13,"Value2", C2:C13)

This formula finds the sum of values in C2:C13 where the corresponding value in A2:A13 contains “Value1” OR where the corresponding value in B2:B13 contains “Value2.” The addition sign serves as the implicit OR operator linking the two conditional sums.

Dataset Preparation for Practical Examples

To demonstrate the practical application of both Method 1 and Method 2, consider the following sports dataset. This dataset tracks player performance attributes, which we will use to calculate totals based on criteria such as team affiliation or player position.

The dataset includes columns for Team (A), Position (B), and Points (C), along with auxiliary columns not shown. We will use the Points column as the primary sum range for both examples.

This table provides a clear context for how our conditional formulas will selectively extract and aggregate data based on multiple criteria simultaneously.

Example 1: SUMIFS with OR (Single Column Criteria)

In this first practical scenario, we aim to calculate the total points scored by players belonging to either the “Mavs” or the “Rockets.” Since both criteria are checked against the single Team column, Method 1—the SUM (3) / SUMIFS (6) array constant technique—is the appropriate and most efficient solution.

We utilize the array constant {“Mavs”,”Rockets”} within the SUMIFS function (7), instructing Excel (2) to calculate the sum of points for “Mavs” and the sum of points for “Rockets” separately. The outer SUM (4) function aggregates these two subtotals into the final result.

The specific formula applied to our dataset is:

=SUM(SUMIFS(C2:C13, A2:A13,{"Mavs","Rockets"}))

The result of this calculation is visualized below, demonstrating how the array successfully handles the OR condition:

Upon execution, the total sum of the values in the Points column for players associated with the “Mavs” or the “Rockets” is calculated to be 53. This confirms the effectiveness of the array constant approach for simple OR logic within a single criterion column.

Example 2: SUMIF with OR (Multiple Column Criteria)

In our second scenario, we need to sum the points based on criteria spread across two distinct columns. We want the total points scored by players where the Team column equals “Mavs” OR the Position column equals “Center.” This requires Method 2, the additive approach using two separate SUMIF (6) functions.

The first SUMIF function (7) calculates the sum of points for all players on the “Mavs.” The second SUMIF function (8) calculates the sum of points for all players listed as “Center.” The crucial addition sign (+) merges the results, acting as the OR logical operator (2).

The key difference here is the structure of the criteria ranges: the first sum uses A2:A13 (Team) as its criteria range, while the second uses B2:B13 (Position) as its criteria range. Both, however, sum the values from C2:C13 (Points).

The resulting formula is:

=SUMIF(A2:A13,"Mavs",C2:C13)+SUMIF(B2:B13,"Center",C2:C13)

The execution of this formula within the spreadsheet environment is shown in the following screenshot:

The sum of the values in the Points column where the value in the Team column is equal to “Mavs” or the value in the Position column is “Center” is 87.

Summary and Further Considerations

Implementing OR logic within conditional summing functions in Excel (3) requires moving beyond the basic `SUMIF` structure. By leveraging the array capabilities of SUMIFS (8) (Method 1) or employing simple functional addition (Method 2), analysts can flexibly aggregate data based on complex criteria.

Method 1 is generally preferred for its conciseness when dealing with multiple criteria against a single range, as it only requires one formula construction. Method 2 is indispensable when the criteria necessarily span multiple, disparate columns. It is crucial to remember the potential for double-counting in Method 2 if rows might satisfy both independent conditions, a factor that should guide formula selection depending on the required outcome.

For those seeking to explore more nuanced conditional summing techniques, particularly those involving more sophisticated AND/OR combinations or handling overlaps in the additive method, advanced array formulas or utilizing `SUMPRODUCT` can offer even greater flexibility and control.

The following list provides links to tutorials explaining how to perform other common Excel (4) conditional operations: