18.4 C
London
Saturday, July 19, 2025
HomeSoftware TutorialsExcelExcel: How to Use SUMIF with Multiple Ranges

Excel: How to Use SUMIF with Multiple Ranges

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

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.

Additional Resources

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

How to Use SUMIF with OR in Excel
How to Use Wildcard in SUMIFS Function in Excel
How to Use SUMIFS with a Date Range in Excel

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories