You can use the following basic syntax to perform a SUMIF function with multiple ranges in Excel:
=SUMIF(A2:A11,G2,B2:B11) + SUMIF(A2:A11,G2,C2:C11)
This particular formula adds the values in the ranges B2:B11 and C2:C11 where the corresponding values in the range A2:A11 are equal to G2.
The following example shows how to use this syntax in practice.
Example: Using SUMIF with Multiple Ranges in Excel
Suppose we have the following dataset that shows the number of points scored by various basketball players during two different games:
Now suppose we would like to calculate the total sum of all points scored between both games for only the players on the Mavs team.
We can use the following formula to do so:
=SUMIF(A2:A11,E2,B2:B11) + SUMIF(A2:A11,E2,C2:C11)
We’ll type this formula into cell F2 and then press Enter:
We can see that the total sum of all points scored between both games for the players on the Mavs team was 140.
We can verify this is correct by manually taking the sum of all points values for the Mavs players between the two games:
Total Points: 22 + 20 + 15 + 32 + 12 + 14 + 10 + 15 = 140.
This matches the value that we calculated using our formula.
Note: In this example, we used the SUMIF() function with two cell ranges, but we can use similar syntax to include any number of cell ranges that we’d like.
The following tutorials explain how to perform other common tasks in Excel: