Below is a dataset of sales information. The dataset has three columns: Region, Product Name, and Price. Each column represents the sales amount of a particular product in a different region.

### Method 1 – Using Multiple SUMIF with OR

**Steps:**

- Select the cell where you want to place your result.
- Enter the following formula:

`=SUMIF(B4:B14,F4,D4:D14)+SUMIF(B4:B14,F5,D4:D14)`

I wanted the sum from the USA or Canada, so I used these two regions as criteria. In the first SUMIF function, the USA is the criterion given in range B4:B14 to extract the sum from the sum_range D4:D14. Then, I wrote the **SUMIF** function again, using criteria **Canada **was given the range **B4:B14 **within the **sum_range D4:D14**.**
**To apply

**OR**logic, add both separate

**SUMIF**formulas.

- Press the
**ENTER**key. You will see that the formula summed the values for the**USA**and**Canada**using**OR**logic.

**Method 2 – Using Multiple SUMIF with OR on Different Columns**

**Steps**:

- Select the cell where you want to place your resultant value.
- Enter the following formula:

`=SUMIF(B4:B14,F4,D4:D14)+SUMIF(C4:C14,G4,D4:D14)`

Here, I want to sum up the price for the laptop or product from the USA region. So, I’ve used the criteria USA from the Region column and Laptop from the Product Name column. In the first SUMIF function, the USA is the criterion given in range B4:B14 to extract the sum from the sum_range D4:D14. Then, I wrote the SUMIF function again, using the criteria Laptop and given the range B4:B14,** **where the **sum_range **was** D4:D14**.**
**To apply

**OR**logic, add both separate

**SUMIF**formulas.

- Press the
**ENTER**key. The formula will sum the values of two different columns using**OR**logic.

**Method 3 – Using SUM within SUMIF OR with an Array**

**Steps**:

- Select the cell to place your resultant value.
- Enter the following formula:

`=SUM(SUMIF(B4:B14,{"USA","Canada"},D4:D14))`

Here, I used the USA and Canada as criteria. Now, the SUMIF function takes the USA and Canada as an array in the criteria given the range B4:B14 and where the sum_range is D4:D14. Then, it will sum the values if at least one of the conditions/criteria is met.

- Press the
**ENTER**key. The formula that summed the values when one criterion was fulfilled will appear.

### Method 4 – Using SUMIF OR with Multiple Criteria

**Steps**:

- Select the cell to place your resultant value.
- Enter the following formula:

`=SUM(SUMIF(B4:B14,F4:G6,D4:D14))`

Here, I used the **USA**, **Canada, **and **Germany** as** criteria**. Now, in the **SUMIF** function, I took the criteria range F4:G6 given the range B4:B14 to generate a sum from the **sum_range D4:D14**.

Then, the **SUM **function will sum the values if at least one of the conditions/criteria is met.

- Press the
**ENTER**key. You will see that the used formula summed the values for the criteria range.

**Method 5 – Using SUMIF OR with SUMPRODUCT**

**Steps**:

- Select the cell to place your resultant value.
- Enter the following formula:

`=SUMPRODUCT(SUMIF(C4:C14,F4:F5,D4:D14))`

Here, as I want to sum the Price for the product Laptop or Computer, I’ve used the Laptop and Computer from the Product Name column as criteria. Now, in the SUMIF function, I took the criteria range F4:F5 given the range C4:C14,** **where the **sum_range **was **D4:D14**. Then, the **SUMPRODUCT **function will sum the values if at least one of the conditions/criteria is met.

- Press the
**ENTER**key. You will see that the used formula summed the values for the criteria range.

### Method 6 – Using SUMIF OR with Asterisk (*)

**Steps:**

- Select the cell to place your resultant value.
- Enter the following formula:

`=SUMIF($C$4:$C$14,F4&"*",$D$4:$D$14)`

Here, you can get the price for all types of laptops from the product name. I used the laptop as the criteria, and I used an asterisk (*) with the criteria. Here, an **asterisk (*) **will search or look up a text with a partial match. Now, in the **SUMIF** function, the range C4:C14 was given, and the **sum_range **was **D4:D14**. Then, it will sum the values if at least one of the partial criteria is met.

- Press the
**ENTER**key. You will see that the formula used summed the values where partial criteria are matched.

**Method 7 – Using SUM & SUMIFS with OR**

**Steps**:

- Select the cell to place your resultant value.
- Enter the following formula in the selected cell or into the
**Formula Bar:**

`=SUM(SUMIFS(D4:D14,C4:C14,{"laptop","computer"}))`

Here, I want the sum of the Price using either a laptop or a computer. I used the Laptop and Computer as criteria1. Now, in the SUMIFS function, the sum_range is D4:D14, and the criteria_range1 is **C4:C14**. Then, the **SUM **function will sum the values if at least one of the criteria is met.

- Press the
**ENTER**key. You will see that the formula used summed the values where the criteria matched.

**Method 8 – Using SUM & SUMIFS on Column**

**Steps**:

- Select the cell where you want to place your result.
- Enter the following formula:

`=SUM(SUMIFS(E4:E14,C4:C14,{"laptop","computer"},D4:D14,"yes"))`

Here, I used **Laptop** and **Computer** as** criteria **from a different column. In the SUMIFS function, take sum_range D4:D14 and criteria_range1 given the range C4:C14. In the **criteria1 **field, laptops and computers were used as **wildcards**. Then, in **criteria_range2, **given the range **D4:D14, **select the **critera2 **for **Yes **from the ** Collected **column. Then, the

**SUM**function will sum the values if at least one of the criteria is met.

- Press the
**ENTER**key. You will see that the used formula summed the values where different column values met at least one of the criteria.

**Method 9 – Using SUM & SUMIFS with Wildcards**

**Steps**:

- Select the cell where you want to place your resultant value.
- Enter the following formula:

`=SUM(SUMIFS(D4:D14,C4:C14,{"*laptop*","*iphone*"}))`

Here, I wanted to sum up the price for a laptop or iPhone using the product name. So that. I used the **Laptop **and **iPhone** as** criteria1 **with **the** **wildcards** **asterisk (*)**. In the **SUMIFS** function, the sum_range D4:D14 is given to extract the price for the given **criteria_range1** **C4:C14**. Then, the **SUM **function will sum the values if at least one of the criteria is fully/ partially met.

- Press the
**ENTER**key. You will see that the used formula summed the values where partial or full criteria are matched.

### Method 10 – Using SUM & SUMIFS with Multiple Criteria

**Steps**:

- Select the cell to place your resultant value.
- Enter the following formula in the selected cell or into the
**Formula Bar:**

`=SUM(SUMIFS(D4:D14,C4:C14,{"laptop","iphone"},B4:B14,{"USA";"Canada"}))`

Here, I used **Laptop** and **iPhone **from ** Product Name, **the

**USA,**and

**Canada**from

**as**

*Region***criteria**from a different column. Now, in the SUMIFS function, take sum_range D4:D14, and in criteria_range1, select the range C4:C14 where criteria1 used Laptop and iPhone.

Then, in criteria_range2, we have** **given the range **B4:B14 **and selected the **USA **and **Canada **as **critera2**. Then, the **SUM **function will sum the values if at least one of the criteria is met.

Here, I used a single-column array for criteria1 and the semi-colons in the second array constant for criteria2 because the latter represents a vertical array. It works for the Excel “pairs” elements in the two array constants and returns a two-dimensional array of results.

- Press the
**ENTER**key. You will see that the used formula summed the values where different column values met at least one of the criteria.

**Practice Section**

In the worksheet, I’ve provided two extra practice sheets so that you can practice the methods.

**Download Practice Workbook**

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