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. Now, we need to calculate the sum of sales of X in all three companies A, B, and C.
First, select a cell where we want the results of the sum of ‘X’ sales, then apply the 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 are “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.
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
We will now consider a small table with 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. The 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 of 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, it will perform 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 added the criteria X in the curly brackets of an array, adding 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 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.
If we want to perform a sum based on two columns of data, consider the same data we used up to now. We need to add one more column, which is called “Tax”, as below. There is a comment ‘yes’ under the 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 the 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 adds another criteria range: 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 add both.
Things to Remember About SUMIF with OR
- SUMIF follows the AND logic that means it will perform an addition operation when if the criteria match.
- SUMIFS will follow the OR and logic; that is why 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–
