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

**Table of Contents**hide

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

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

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

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

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

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

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

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