Table of Contents
The SUMIF function in Excel can be used with the OR logical operator to add up cells in a range that meet any of multiple criteria. For example, in a SUMIF statement, you could add together all cells in a range based on a value in one column being equal to “apple” OR equal to “banana”. This would add up all the cells that had either “apple” or “banana” in the specified column.
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.
The following tutorials explain how to perform other common SUMIF operations in Excel: