How To Use Sumif Function In Excel

SUMIF with OR (Table of Contents)

SUMIF with OR in Excel
How to Use SUMIF with OR in Excel?

SUMIF with OR Function in Excel

SUMIF is ane of the functions which is very much useful to find the totals of similar values. Information technology reduces the time when we are working with a large amount of data and need to calculate the sum of values of similar nature information. SUMIF is a combination of SUM and IF functions. SUMIF part will perform SUM(addition) when the IF condition satisfies. It is very piece of cake to utilise.

Syntax of the SUMIF function

Range – A range of cells on which the criteria or condition is to be applied. The range tin can include a number, cell references, and names every bit well.
Criteria – It is the status 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?

Allow's understand how to use SUMIF with OR Function in Excel using some examples.

You can download this SUMIF with OR Excel Template hither – SUMIF with OR Excel Template

SUMIF with OR – Example #one

Consider a table having the sales data of companies A, B, and C for products 10, Y, and Z.

In the to a higher place screenshot, we can observe the sales of products X, Y, and Z. At present, we need to summate the sum of sales of Ten in all three companies A, B, and C.

First, select a cell where we want the results of the sum of '10' sales, then apply the office and select the range.

Here the range is from B2 to B12, so select that range then the function will automatically selection B2:B12 as shown in the to a higher place screenshot. One time the range is picked, give the comma equally per syntax. Later give the "Criteria" here. Criteria are "X" equally we want to detect the SUM of Ten production sales, so requite 10 and over again comma.

Final, we need to select the sum_range; here, sales is the range which nosotros need to add whenever the production is X; hence select the sales range from C2:C12 every bit shown in the below screenshot.

The sum of sales of 10 across all the iii companies is 11166.

Similarly, we can find the sales of Y and Z also.

Make sure you lot are using the commas and a double column for criteria; otherwise, the formula will throw an fault. Normally SUMIF volition work on the logic, AND hence that is the reason where ever the criteria match, it volition perform the addition and return the results.

By using normal SUMIF, we volition be able to perform SUM functioning for only unmarried criteria. If we are using OR logic, then we tin perform SUM adding for dual criteria.

For using OR logic, we should use SUMIFS instead of SUMIF because SUMIF can perform with single criteria, only SUMIFS tin perform on multiple criteria equally 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 chore is computing the full value of sales, whether it is through straight or online. Now we will apply the SUMIFS formula to observe the total sales.

It is a fleck different from the SUMIF every bit 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.

Find the in a higher place screenshot the quantity is the cavalcade nosotros need to add; hence select the cells from C2 to C10 as sum_range. The next step is the pick of criteria_range1.

Here Criteria is "Sales through direct" and "Sales through online"; hence nosotros need to select the column B data from B2 to B10.

Later we demand to give the Criteria1 and so criteria _range2, criteria ii, only here we volition practise a minor alter. Nosotros will give criteria1 and criteria2 in a curly bracket like an array.

See, we got the result equally 1438; allow'southward check whether it picked the total correctly or non. Apply a filter and filter only sales through direct and sales through online and select the unabridged quantity and detect the full at the bottom of the screen. Observe the below screenshot; I accept highlighted the count and sum of the values.

So, the full should be 2274, but we got the event of 1438. And so how this 1438 comes and what sum is this.

Find the above screenshot that 1438 is the total sales for sales through straight. The formula did not choice the sales through online considering we gave the formula in a different format that is like an assortment. 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 volition explicate why we used another SUM function and how it works. When we gave SUMIFS part with two criteria as in the form of an assortment, information technology volition calculate the sum of sales through straight and online separately. To become the sum of both, we take used some other SUM office which will add together the sum of ii sales. If we want to add together one more criteria, we tin 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 10 to the existing sum quantity.

In case if you desire to use only SUMIF and do not desire to utilise SUMIFS, and then apply the formula in beneath fashion.

Observe the formula in the above screenshot. In this case, get-go, nosotros gave the criteria range and and then criteria1 and criteria2 and the final sum_range.

If we want to perform a sum based on two columns of data, consider the same information we used upward to at present. We need to add i more than column, which is called "Tax", as below. There is a annotate 'yes' under the Tax column.

At present the task is to summate the sum of quantity for the sales through straight and sales through online, which has "Yep" under the tax column.

Apply the formula equally shown in the beneath 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 bank check for criteria ane with yes and criteria ii with yes and add together both.

Things to Recall Almost SUMIF with OR

SUMIF follows the AND logic that means it will perform an improver functioning when if the criteria lucifer.
SUMIFS will follow the OR and logic; that is why we can perform multiple criteria at a time.