# How to Apply SUMIF with Multiple Ranges in Excel

Get FREE Advanced Excel Exercises with Solutions!

The SUMIF function is widely used in Excel for calculating the sum value of a single range based on a given criterion. But it is also possible to apply SUMIF on multiple ranges as well though it is not a well-known approach. In this article, I will share with you some key tricks on how we can apply SUMIF on multiple ranges in Excel. So let’s begin our journey.

## How to Apply SUMIF with Multiple Ranges in Excel: 3 Easy Methods

In this section, we will demonstrate 3 effective methods to apply SUMIF with multiple ranges in Excel with appropriate illustrations. But before that, let’s have a look at the following dataset. We have the items’ names, order dates, the purchase price for three months i.e. January, February & March, and the discount. Now, we want to sum multiple ranges or columns based on items using the SUMIF function. For example, let’s say we want to get the total price of Apples by adding the price in the January, February, March, and Discount columns using the SUMIF function. We can do that using 3 methods. Let’s begin with the first one.

### 1. Use of Helper Column to Sum Multiple Ranges

The easiest way to sum multiple ranges using the SUMIF function is to add an extra column known as the helper column. In the helper column, we will add all the values in a row. For example, on the 5th row (R5), we add Apple’s purchased price in January, February, and March, and discount and get a total of 102.

Steps:

• Go to cell H5 and write the following formula.
`=SUM(D5:G5)` • Here, we have added all the values in the first row. Now, we use the Fill Handle to auto-fill the rest of the cells. • Now that we have the helper column, let’s use the criteria in cell J7 to evaluate the total price for Apple by applying the SUMIF function. • Here in cell K7, write the following formula.
`=SUMIF(B5:B16,\$J\$6,H5:H16)` This method is only applicable when we can add an additional column. But, when it is not possible to add an extra column, we have to use the methods below.

Read More: SUMIF Across Multiple Sheets in Excel

### 2. Applying the SUM Function to SUMIF with Multiple Ranges

In this approach, instead of using a helper column, we will use the SUMIF function multiple times, and then the results will be added together using the SUM function. Follow the steps below.

Steps:

• Write the following formula in cell K6 and press Enter key.
`=SUM(SUMIF(B5:B16,J6,D5:D16),SUMIF(B5:B16,J6,E5:E16),SUMIF(B5:B16,J6,F5:F16),SUMIF(B5:B16,J6,G5:G16))` 🔎How Does the Formula Work?

• SUMIF(B5:B16,J6,D5:D16)

This part sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of January (D5:D16).

• SUMIF(B5:B16,J6,E5:E16)

It sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of February (E5:E16).

• SUMIF(B5:B16,J6,F5:F16)

This part sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of March (F5:F16).

• SUMIF(B5:B16,J6,G5:G16)

It sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the Discount column (G5:G16).

• SUM(SUMIF(B5:B16,J6,D5:D16),SUMIF(B5:B16,J6,E5:E16),SUMIF(B5:B16,J6,F5:F16),SUMIF(B5:B16,J6,G5:G16))

It sums up all four results obtained by the above 4 functions.

### 3. SUMIF with Multiple Ranges Using Addition Operator

Instead of using the SUM function, we can manually add the results that we get by using the SUMIF function multiple times in a formula.

Steps:

• Write the following formula in cell K6 and then press Enter key.
`=SUMIF(B5:B16,J6,D5:D16)+SUMIF(B5:B16,J6,E5:E16)+SUMIF(B5:B16,J6,F5:F16)+SUMIF(B5:B16,J6,G5:G16))` 🔎 How Does the Formula Work?

• SUMIF(B5:B16,J6,D5:D16)

It sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of January (D5:D16).

• SUMIF(B5:B16,J6,E5:E16)

This part sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of February (E5:E16).

• SUMIF(B5:B16,J6,F5:F16)

This part sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the month of March (F5:F16).

• SUMIF(B5:B16,J6,G5:G16)

Sums up the total price of Apple (J6) by looking up the criteria in the B5:B16 range for the Discount column (G5:G16).

• SUMIF(B5:B16,J6,D5:D16)+SUMIF(B5:B16,J6,E5:E16)+SUMIF(B5:B16,J6,F5:F16)+SUMIF(B5:B16,J6,G5:G16))

It sums up all four results obtained by the above 4 functions.

## Things to Remember

• Use the 1st method when you can add an additional column
• Use the 2nd and 3rd methods when you have a small number of ranges to sum

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  