How to Use SUMIF with OR Logic in Excel (10 Suitable Methods)

Get FREE Advanced Excel Exercises with Solutions!

By using the SUMIF function you can sum values based on a single condition or criteria like other cells being equal to either one value or another. You can use the SUMIF OR logic, whenever you need to sum values based on multiple criteria where at least one of the conditions are met.

To explain how does SUMIF OR works, I’m going to use a sample dataset of sales information. There are 3 columns in the dataset. These columns represent the sales amount of a particular product in a different region. These columns are Region, Product Name, and Price.

SUMIF with OR


Download Practice Workbook


10 Methods to Use SUMIF with OR Logic in Excel

1. Using Multiple SUMIF with OR

You can use the SUMIF function with OR logic by using the formula according to the criteria. If you want to use multiple criteria, then need to add the SUMIF function multiple times.

First, select the cell where you want to place your result.

Then, type the following formula.

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

Using Multiple SUMIF with ORHere, I wanted the sum from either USA or Canada so used these two regions as criteria. Now, in the first SUMIF function taken the USA as criteria given the range B4:B14 to extract the sum from the sum_range D4:D14. Then wrote the SUMIF function again, this time using criteria Canada was given the range B4:B14 within the sum_range D4:D14.
To apply OR logic then add both the separate SUMIF formulas.

Finally, press the ENTER key. Then you will see that the used formula summed the value for both USA and Canada using OR logic.


2. Using Multiple SUMIF with OR on Different Column

You also can use the SUMIF OR on a different column. You can select criteria from different columns.

Firstly, select the cell where you want to place your resultant value.
Then, type the following formula.

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

Using Multiple SUMIF with OR on Different ColumnHere, I want to sum Price for the product from USA region or Laptop. So, I’ve used the criteria the USA from Region column and Laptop from Product Name column. Now, in the first SUMIF function taken the USA as criteria given the range B4:B14 to extract the sum from the sum_range D4:D14. Then wrote the SUMIF function again, this time using criteria Laptop and given the range B4:B14 where the sum_range was D4:D14.
To apply OR logic then add both the separate SUMIF formulas.

In the end, press the ENTER key. Now, you will see that the used formula summed the value of two different columns using OR logic.


3. Using SUM within SUMIF OR with an Array

You can use the SUM function within SUMIF OR with an array. While you can give more than one criteria within the array.

First, select the cell to place your resultant value.
Next, type the following formula.

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

Using SUM within SUMIF OR with an ArrayHere, I used USA and Canada as criteria. Now, in the SUMIF function taken the USA and Canada as an array in the criteria given the range B4:B14 and where the sum_range was D4:D14. Then, it will sum the values if at least one of the conditions/criteria is met.

Finally, press the ENTER key.
Hence, you will see that the used formula summed the values when one criterion is fulfilled.


4. Using SUMIF OR with Multiple Criteria

In the SUMIF function with OR logic, you also can use multiple criteria.

To begin with, select the cell to place your resultant value.
After that, type the following formula.

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

Using SUMIF OR with Multiple CriteriaHere, I used the USA, Canada, and Germany as criteria. Now, in the SUMIF function taken the criteria range F4:G6 given the range B4:B14 for generating 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.

In the end, press the ENTER key.
Therefore, you will see that the used formula summed the values for the criteria range.


5. Using SUMIF OR with SUMPRODUCT

You can use the SUMPRODUCT function to perform SUMIF with OR like operation.

First, select the cell to place your resultant value.
Then, type the following formula.

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

Using SUMIF OR with SUMPRODUCTHere, As I want to sum the Price for the product Laptop or Computer, so I’ve used the Laptop and Computer from Product Name column as criteria. Now, in the SUMIF function taken 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.

Finally, press the ENTER key.
As a result, you will see that the used formula summed the values for the criteria range.


6. Using SUMIF OR with Asterisk (*)

By using the Asterisk(*) with the SUMIF function you can perform OR logic.
For that, I am using this sample dataset where I have some partial values in the columns.

Using SUMIF OR with Asterisk (*)

Firstly, select the cell to place your resultant value.
Secondly, type the following formula.

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

Here, to get the Price for all types of laptops from the Product Name. I used the Laptop as criteria, where used an asterisk (*) with the criteria. Here, an asterisk (*) will search or lookup up for a text with a partial match. Now, in the SUMIF function given the range C4:C14 where the sum_range was D4:D14. Then, it will sum the values if at least one of the partial criteria is met.

In the end, press the ENTER key.
Thus, you will see that the used formula summed the values where partial criteria is matched.

Using SUMIF OR with Asterisk (*)


7. Using SUM & SUMIFS with OR

You can use the SUM function within the SUMIFS function to use the OR logic.

First, select the cell to place your resultant value.
Then, type the following formula in the selected cell or into the Formula Bar.

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

Using SUM & SUMIFS with ORHere, by using either Laptop or Computer I want the sum of the Price. I used the Laptop and Computer as criteria1. Now, in the SUMIFS function given the sum_range D4:D14 and the criteria_range1 was C4:C14. Then, the SUM function will sum the values if at least one of the criteria is met.

Press the ENTER key.
Thus, you will see that the used formula summed the values where criteria are matched.


8. Using SUM & SUMIFS on Column

You also can use the SUM function within the SUMIFS function with OR on a different column. You can select criteria from different columns.

Firstly, select the cell where you want to place your result.
Then, type the following formula.

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

Using SUM & SUMIFS with WildcardsHere, I used Laptop and Computer as criteria from a different column. Now, in the SUMIFS function taken sum_range D4:D14 and in criteria_range1 given the range C4:C14. In the criteria1 field, used laptop and computer as wildcards. Then in criteria_range2 given the range D4:D14 and selected 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.

In the end, press the ENTER key. Now, you will see that the used formula summed the values where different columns values met at least one of the criteria.


9. Using SUM & SUMIFS with Wildcards

In the SUMIFS function with OR logic, you also can use wildcards.
Here, I used the asterisk(*) to perform OR logic.

To begin with, select the cell where you want to place your resultant value.
Then, type the following formula.

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

Using SUM & SUMIFS with Multiple CriteriaHere, I wanted to sum the Price for Laptop or iPhone from the Product Name. So that. I used the Laptop and iPhone as criteria1 with the wildcards asterisk (*). Now, in the SUMIFS function given the sum_range D4:D14 to extract the price for given criteria_range1 C4:C14. Then, the SUM function will sum the values if at least one of the criteria fully/ partially is met.

Press the ENTER key.
Thus, you will see that the used formula summed the values where partial or full criteria are matched.


10. Using SUM & SUMIFS with Multiple Criteria

You can use the SUM function within the SUMIFS function with OR logic for multiple criteria.

First, select the cell to place your resultant value.
Then, type the following formula in the selected cell or into the Formula Bar.

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

Using SUM & SUMIFS with Multiple CriteriaHere, I used Laptop and iPhone from Product Name, the USA, and Canada from Region as criteria from a different column. Now, in the SUMIFS function taken sum_range D4:D14 and in criteria_range1 selected the range C4:C14 where in criteria1 used Laptop and iPhone.

Then in criteria_range2 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 it represents a vertical array.
It works for the  Excel “pairs” elements in the two array constants and returns a two-dimensional array of results.

In the end, press the ENTER key. Now, you will see that the used formula summed the values where different columns 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 these explained methods.

Practice Sheet1 for SUMIF OR


Conclusion

In this article, I’ve explained 10 methods of SUMIF OR in Excel. You can follow any of the methods to perform SUMIF with OR logic. Also, I explained how you can hide unwanted empty rows. In case you have any confusion or question regarding these methods you may comment down below.

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo