Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Apply SUMIF with Multiple Ranges in Excel

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.


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.

SUMIF Multiple Ranges

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)

Use of Helper Column to Sum Multiple Ranges

  • 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)

Use of Helper Column to Sum Multiple Ranges

  • 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))

Applying the SUM Function to SUMIF Multiple Ranges

🔎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))

SUMIF Multiple Ranges Using Addition Operator

🔎 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.


Further Readings

Md. Abdul Kader

Md. Abdul Kader

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

Leave a reply

ExcelDemy
Logo