**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 sumif 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 sumif multiple ranges in Excel. So let’s begin our journey.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**3 Easy Methods to Apply SUMIF with Multiple Ranges**

In this section, we will demonstrate **3** effective methods to sumif 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**, **March**, and **Discount** and get a total of **102**. Follow the steps below

**Steps:**

- First of all, 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 rest of the cells.

- Now that we have the helper column, let’s use the criteria in
**J7**to evaluate the total price for**Apple**by applying**the SUMIF function**. (see figure below)

- Here in
**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.

### 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:**

- First, write the following formula in cell
**K6**and then 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. Follow the steps below.

**Steps:**

- Firstly, 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

**Conclusion**

That is the end of this article. If you find this article helpful in understanding how to use SUMIF with multiple ranges in excel then please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit **Exeldemy** for more exciting articles on **Excel**.