How to Use SUMIF with OR in Excel


You can use the following formulas to combine the SUMIF function with the OR function in Excel:

Method 1: SUMIF with OR (One Column)

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

This particular formula finds the sum of values in B2:B13 where the corresponding value in A2:A13 contains “Value1”, “Value2”, or “Value3.”

Method 2: SUMIF with OR (Multiple Columns)

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

This particular formula finds the sum of values in C2:C13 where the corresponding value in A2:A13 contains “Value1” or the corresponding value in B2:B13 contains “Value2.”

The following example shows how to use each method in practice with the following dataset in Excel:

Example 1: SUMIF with OR (One Column)

We can use the following formula to sum the values in the Points column where the value in the Team column is equal to “Mavs” or “Rockets”:

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

The following screenshot shows how to use this formula in practice:

The sum of the values in the Points column where the value in the Team column is equal to “Mavs” or “Rockets” is 53.

Example 2: SUMIF with OR (Multiple Columns)

We can use the following formula to sum 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”:

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

The following screenshot shows how to use this formula in practice:

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.

Additional Resources

The following tutorials explain how to perform other common SUMIF operations in Excel:

How to Use SUMIFS with a Date Range in Excel
How to Sum If Cells Contain Text in Excel

Leave a Reply

Your email address will not be published.