SUMIF Multiple Ranges [6 Useful Ways]

SUMIF function is widely used for calculating the sum value from different aspects depending on criteria also.

In this article, we’ll discuss the useful ways to find the sum value using the SUMIF function based on mainly multiple ranges.

Download Practice Workbook

 

Basics Before Calculating SUMIF Multiple Ranges

This section is mainly for beginners. Here, we’ll understand the SUMIF function and the process of entering the formula in Excel.

What is SUMIF Function

The SUMIF function is an Excel function that helps to add all numbers to cells according to one criterion.

This function is available from earlier versions of Excel (e.g. Excel 2003) to the updated versions (e.g. Microsoft 365).

The syntax of the function is

=SUMIF (range, criteria, [sum_range])

There are the following arguments in the function.

range – The range of cells that you want to apply the criteria against.

criteria – The criteria used to determine which cells to add.

sum_range – [optional] The cells to add together.

How to Enter Formula in Excel

Entering a formula in the Excel formula bar is quite a simple task. First, you have to select a blank cell where you want to show the output. Then input an equal sign (=). And then insert the formula with proper parenthesis. If you want to use the cell value as an absolute cell reference, input a dollar sign ($) that won’t change the value. At last, press Enter. Moreover, if you want to use the same formula for the next cells, just use the Fill Handle Tool.

SUMIF Multiple Ranges

Let’s have a look at the following dataset. We have the name of items along with order date, purchase price at three months i.e. January, February & March, and discount.

Dataset SUMIF Multiple Ranges

Now, we’ll find the sum value based on multiple ranges using the SUMIF function.

Let’s get started.

1. SUMIF Multiple ranges with Single Criteria

If you want to calculate the total price of items regarding multiple ranges e.g. purchase price and discount, you may utilize the following formula.

It is mentionable that the actual price is the sum of the purchase price and discount.

Read more: SUMIF with Multiple Criteria

The  formula is

=SUMIF($B$5:$B$16,$I5,$D$5:$D$16)+SUMIF($B$5:$B$16,$I5,$G$5:$G$16)

Here, B5:B16 is the cell range of items, I5 is the cell of Apple “item”, D5:D16 is the range of purchase price at January, and G6:G16 is the cell range of discount.

The syntax SUMIF($B$5:$B$16,$I5,$D$5:$D$16) returns the total sum of the purchase price at January to every item. Besides, SUMIF($B$5:$B$16,$I5,$G$5:$G$16) returns the total sum of discount to every item.

SUMIF Multiple ranges with Single Criteria

2. SUMIF Multiple Ranges Using AND Logic

Let’s imagine, you want to find the sum value of an item using AND logic and multiple ranges.

For this, you may proceed with the following formula.

=SUMIF(B5:B16,J5,D5:D16)

In this formula, B5:B16 is the range of items, J5 is the item name Apple, D5:D16 is the range of purchase price at January.

SUMIF Multiple Ranges Using AND Logic

3. SUMIF Multiple Ranges Using OR Logic

Using OR Logic, you can set multiple criteria. For example, you can find the total discount of two items namely Apple and Banana. In this method, several SUMIF functions return the sum value and then, we can combine the all outputs.

Insert the following formula.

=SUMIF(B5:B16,J5,G5:G16)+SUMIF(B5:B16,J6,G5:G16)

Here, B5:B16 is the range of items, J5 is the item name Apple, G5:G16 is the range of discount, and J6 is the item name Banana

The first syntax =SUMIF(B5:B16,J5,G5:G16) will return the sum discount for Apple and the second syntax SUMIF(B5:B16,J6,G5:G16) will return the sum discount for Banana.

SUMIF Multiple Ranges Using OR Logic

4. SUMIF Multiple Ranges for Both Columns & Rows

You can easily calculate the total purchase price at March (Column F in the data set) for the two items namely Apple (Row 1) and Banana (Row 2).

For that, you may follow the formula

=SUMIF(B5:B16,J5,F5:F16)+SUMIF(B5:B16,J6,F5:F16)

In this formula, B5:B16 is the range of items, J5 is the item name Apple, F5:F16 is the range of purchase price in March, and J6 is the item name Banana.

The first syntax =SUMIF(B5:B16,J5,F5:F16) will return the total purchase price at March for Apple and the last syntax SUMIF(B5:B16,J6,F5:F16) will return the total purchase price at March for Banana.

SUMIF Multiple Ranges for Both Columns & Rows

5. SUMIF Multiple Ranges Using Array Function

You can perform multiple calculations simultaneously with the array function or, it can perform several calculations multiple times within a selected cell range.

In this data set, you can imagine Apple and Banana as an array. Then, input the following formula.

{=SUM(SUMIF($B$5:$B$16,$I6:$J6,$G$5:$G$16))}

In this formula, B5:B16 is the range of items, I6:J6 is the array of Apple and Banana, G5:G16 is the range of discounts.

Besides, the SUMIF function will return another array that contains the sum matching the criteria. Then the SUM function will add the elements of that array and provide the result.

Note. In the array formula, you have to press CTRL+SHIFT+ENTER instead of just pressing ENTER

SUMIF Multiple Ranges Using Array Function

6. SUMIF Multiple Ranges Based on Date Range

Lastly, you may calculate the sum value based on date range. In this way, there will be a start date and an end date.

You’ll get the total discount within a specific period if you use the following formula.

=SUMIF(C5:C16,">="&J5,G5:G16)-SUMIF(C5:C16,">"&K5,G5:G16)

Here, C5:16 is the range of order date, J5 is the start date, G5:G16 is the range of discount, and K5 is the end date.

The first syntax will find the total discount at the start date and before the start date whereas the last syntax will find the total discount after the end date.

Later, if we subtract the value of the last syntax from the first syntax we’ll get the range value between the start date and end date.

SUMIF Multiple Ranges Based on Date Range

Conclusion

Now, you’ve got these examples of calculating the sum value based on multiple ranges using the SUMIF function. I think you’ll not face any trouble regarding this topic. However, if you have any issues, please share them with me in the following comments section.


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