SUMIF with OR (Table of Contents)
SUMIF with OR Function in Excel
SUMIF is one of the functions which is very much useful to find the totals of similar values. It reduces the time when we are working with a large amount of data and need to calculate the sum of values of similar nature data. SUMIF is a combination of SUM and IF functions. SUMIF function will perform SUM(addition) when the IF condition satisfies. It is very easy to apply.
Syntax of the SUMIF function
- Range – A range of cells on which the criteria or condition is to be applied. The range can include a number, cell references and names as well.
- Criteria – It is the condition in the form of number, expression or text that defines which cells will be added.
- Sum_range – These are actual cells to sum. If omitted, cells specified in a range are used.
How to Use SUMIF with OR Criteria in Excel?
Let’s understand how to use SUMIF with OR Function in Excel using some examples.
SUMIF with OR – Example #1
Consider a table having the sales data of companies A, B, and C for products X, Y, and Z.
In the above screenshot, we can observe the sales of products X, Y and Z are given. Now we need to calculate the sum of sales of X in all the three companies A, B, and C.
First, select a cell where we want the results of the sum of ‘X’ sales then apply function and select the range.
Here the range is from B2 to B12 so select that range then the function will automatically pick B2:B12 as shown in the above screenshot. Once the range is picked give the comma as per syntax. Later give the “Criteria” here Criteria is “X” as we want to find the SUM of X product sales so give X and again comma.
Last, we need to select the sum_range, here sales is the range which we need to add whenever the product is X hence select the sales range from C2:C12 as shown in the below screenshot.
4.8 (2,146 ratings)
View Course
The sum of sales of X across all the three companies is 11166.
Similarly, we can find the sales of Y and Z also.
Make sure you are using the commas and a double column for criteria otherwise the formula will throw an error. Normally SUMIF will work on the logic AND hence that is the reason where ever the criteria match it will perform the addition and return the results.
By using normal SUMIF we will be able to perform SUM operation for only single criteria. If we are using OR logic, then we can perform SUM calculation for dual criteria.
For using OR logic we should use SUMIFS instead of SUMIF because SUMIF can perform with single criteria but SUMIFS can perform on multiple criteria as per our requirement.
SUMIF with OR – Example #2
Now we will consider a small table which has data of sales and revenue through online and direct as below.
Our task is calculating the total value of sales whether it is through direct or online. Now we will apply the SUMIFS formula to find the total sales.
It is a bit different from the SUMIF as in this first we will select the sum range. Here sum range means the column where the values are available to perform addition or sum.
Observe the above screenshot the quantity is the column we need to add hence select the cells from C2 to C10 as sum_range. Next step is the selection of criteria_range1.
Here Criteria is “Sales through direct” and “Sales through online” hence we need to select the column B data from B2 to B10.
Later we need to give the Criteria1 and then criteria _range2, criteria 2 but here we will do a small change. We will give criteria1 and criteria2 in a curly bracket like an array.
See we got the result as 1438 let’s check whether it picked the total correctly or not. Apply a filter and filter only sales through direct and sales through online and select the entire quantity and observe the total at the bottom of the screen. Observe the below screenshot, I have highlighted the count and sum of the values.
So, the total should be 2274 but we got the result as 1438. Then how this 1438 comes and what sum is this.
Observe the above screenshot that 1438 is the total sales for sales through direct. The formula did not pick the sales through online because we gave the formula in a different format that is like an array. Hence if we add one more SUM formula to SUMIFS then it will perform the addition of both criteria.
Observe the formula in the above screenshot one more SUM added to the SUMIF and the result is 2274.
I will explain why we used another SUM function and how it works. When we gave SUMIFS function with two criteria as in the form of an array it will calculate the sum of sales through directly and online separately. To get the sum of both we have used another SUM function which will add the sum of two sales. If we want to add one more criteria we can add it in the same formula.
Observe the formula we just added the criteria X in the curly brackets of an array and it added the quantity X to the existing sum quantity.
In case if you want to use only SUMIF and do not want to use SUMIFS then apply the formula in the below way.
Observe the formula in the above screenshot. In this case first, we gave the criteria range and then criteria1 and criteria2 and the last sum_range.
In case if we want to perform sum based on two columns data consider the same data which we used up to now. We need to add one more column which is called “Tax” as below. There is a comment ‘yes’ under TAX column.
Now the task is to calculate the sum of quantity for the sales through direct and sales through online which has “Yes” under tax column.
Apply the formula as shown in the below screenshot to get the sum of sales which has “Yes” under the Tax column.
After the normal SUMIFS formula just add another criteria range which is tax column range C2 to C10 and give criteria “Yes” in double quotes.
Now the formula will check for criteria 1 with yes and criteria 2 with yes and will add the results of both.
Things to Remember About SUMIF with OR
- SUMIF follows the AND logic that means it will perform addition operation when if criteria matches.
- SUMIFS will follow the OR and AND logic that is the reason we can perform multiple criteria at a time.
Recommended Articles
This is a guide to SUMIF with OR in Excel. Here we discuss how to use SUMIF with OR Criteria in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles–
