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

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

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.

Read More: How to Sum Multiple Columns Based on Multiple Criteria in Excel

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.

Read More: How to Sum Based on Column and Row Criteria in Excel

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

Download Practice Workbook

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

Conclusion

This 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 our website for more exciting articles on Excel.

Related Articles

• SUMIF with Multiple Criteria in Different Columns in Excel
  
• SUMIF and VLOOKUP Functions Across Multiple Sheets in Excel
• How to SUMIF for Multiple Criteria Across Different Sheet in Excel

<< Go Back to SUMIF Multiple Criteria | Excel SUMIF Function | Excel Functions | Learn Excel