EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel Excel SUMIF with OR
Secondary Sidebar
Excel Functions
  • Maths Function in Excel
    • Calculating Investment Return In Excel
    • Sumif Function Examples
    • MROUND in Excel
    • SUM Cells in Excel
    • MMULT in Excel
    • MINVERSE in Excel
    • Trunc in Excel
    • SUMIF with Text
    • COS Function in Excel
    • RANDBETWEEN in Excel
    • Excel SUMIF with OR
    • Excel DEGREES Function
    • Calculations in Excel
    • Sumif Between Two Dates
    • Calculate Income Tax in Excel
    • PRODUCT Function in Excel
    • How to Sum Multiple Rows in Excel
    • Matrix in Excel
    • ROUND Formula in Excel
    • How to SUM in Excel
    • SUMPRODUCT Formula in Excel
    • Excel Running Total
    • Excel Rounding
    • Excel Exponents
    • Excel SIN Function
    • Excel Square Root Function
    • Excel ROUNDUP Function
    • ROUND Excel Function
    • Excel ROUNDDOWN Function
    • ODD Excel Function
    • SUMPRODUCT Function in Excel
    • AGGREGATE Function in Excel
    • ABS Function in Excel
    • Excel TAN Function
    • COMBIN Function in Excel
    • CEILING Function in Excel
    • INT Excel Function
    • SUBTOTAL Function in Excel
    • LN Function in Excel
    • Excel RAND Function
    • TANH Function in Excel
    • SUMIF Function in Excel
    • SUM Function in Excel
    • POWER Function in Excel
    • EVEN Function in Excel
    • LOG Function in Excel
    • FLOOR Excel Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED Certification
  • Excel for HR Training

Excel SUMIF with OR

By Madhuri ThakurMadhuri Thakur

SUMIF with OR 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 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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,898 ratings)

Syntax of the SUMIF function

SUMIF with OR Syntax

  • 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.

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

SUMIF with OR – Example #1

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

SUMIF with OR Data 1

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.

SUMIF with OR Example 1-1

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.

SUMIF with OR Example 1-2

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.

SUMIF with OR Example 1-3

The sum of sales of X across all the three companies is 11166.

SUMIF with OR Example 1-4

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

SUMIF with OR Example 1-5

SUMIF with OR Example 1-6

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.

SUMIF with OR Data 2

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.

Example 2-2

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.

Example 2-3

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.

Example 2-4

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.

Example 2-5

So, the total should be 2274, but we got the result of 1438. Then how this 1438 comes and what sum is this.

Example 2-6

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.

Example 2-7

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.

Example 2-8

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.

Example 2-9

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.

Example 2-10

Apply the formula as shown in the below screenshot to get the sum of sales which has “Yes” under the Tax column.

Example 2-11

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–

  1. SUMIF in Excel
  2. Excel SUMIFS with Dates
  3. OR Excel Function
  4. Sumif Function Examples
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for HR Training (8 Courses, 10+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download SUMIF with OR Excel Template

EDUCBA

Download SUMIF with OR Excel Template

EDUCBA

डाउनलोड SUMIF with OR Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more