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