As a standalone function you might be thinking “what do I need to know this function for?”. On the surface, it seems like a function with little to no use, but its versatility is displayed when combined with other functions such as the IF and the IFS function. The concept can also be applied to otherwise limiting functions that check for single criteria such as the COUNTIF, and SUMIF functions (we would show an example in subsequent sections).
So, what does the OR Function do? The OR Function allows you check for multiple conditions and return TRUE if any of the conditions are met. In today’s post, we would walk you through the OR Function in excel and how you can combine it with some of the other functions previously stated. Before we delve into such examples, let’s take a quick look at the syntax and argument of the OR Function.
Syntax & Argument
The Syntax of the OR Function is as follows:
=OR(logical1, [logical2], …)
There can be as few as one argument and as many as 255 arguments. Each argument represents a condition to check, which if met would return TRUE for the OR function regardless of the other conditions not being met.
- logical1: This represents the first condition to be tested.
- logical2: This is an additional condition you want to test, it is optional, however, it may make no sense if you use the OR function with only one argument as you can very well test that one argument without the use of the OR function.
As previously stated in this section, you can go as high as 255 arguments, and 254 of them are optional because the OR function would work well with just one argument. Now let’s move on to some usage notes for the OR Function.
Usage Notes
- The OR function accepts 255 arguments and each one is to be separated by a comma.
- Each argument of the OR function must evaluate to TRUE or False or be a formula that evaluates to a logical value.
- If the first argument turns out to be TRUE, the OR function returns TRUE for the function as a whole without evaluating the remaining arguments. This is because any one argument being TRUE would suffice, unlike the AND Function which needs to return TRUE for all arguments tested.
- If all the arguments evaluate to FALSE, the OR Function returns a FALSE
- The OR function’s capability can be extended when used in conjunction with other functions, such as IF and the IFS to test multiple conditions and return different results based on the outcome. The SUMIF and COUNTIF functionalities can also be extended by using the concept of the OR function and combining it with a SUM function.
OR Function Examples
Basic Example
OR with Numerical Values
To illustrate the workings of the OR function, let’s assume that we need to check if the values in cells A1, B2 and C3 are greater than 70, we use the following formula:
If any of cells A1, B2 and C3 happen to have a numerical value above 70, the OR function returns TRUE. If none of them have a value above 70, then the OR function returns FALSE.
Or with Text Values
For this section we have a list of precious metals, and we need to use the OR function to check whether it is Gold or Silver
To return a TRUE or FALSE if the cells in range B3:B15 are Gold or Silver, we use the formula below:
Now, copy and paste to the cells below:
OR with Arrays
Instead of selecting each cell to test for a particular condition, you can bulk select by using the array form with the OR function. To illustrate, let’s assume that cells B5:B90 have various numerical values and we need to check if any of the values are greater than 25. To achieve this, we only need to select the range rather than selecting each cell and testing if greater than our target number. Our formula would be as follows:
In subsequent sections, we would combine the OR function with other excel functions such as the IF Function, SUMIF Function and COUNTIF Function. Let’s start with the OR and IF Function combination.
OR and IF Function Combination
The IF Function makes the use of OR function more interesting because it enables us return custom values if TRUE or FALSE. An example is when a student needs to score above 70 in at least one of two courses. If this happens, we want to return a Pass, but if the student fails to get this score, we want to return a Fail. Let’s assume that the two scores are on cells A2 and B2, we would use the formula below to achieve our target.
OR and IFS function Combination
The IFS function is an alternative to the Nested IF which can get confusing especially for newbies to excel. With the IF Function, you can test multiple condition at once and return a value that corresponds to the first TRUE condition. Now, let’s assume that a score between 70 – 100 gets you a distinction, 50 – 69 gets you a merit and below 50 is a fail, we can use the OR Function to return these values
To be honest, this is easier to achieve with the AND and IFS Function combination, but it is also achievable with the OR and IFS Functions combination, let’s see the steps below:
Step 1: We would need to define the OR function for the lowest grade first (fail). For this, we need only one argument which is any number less than 50:
Please note that we are taking it in this Order because the IFS function tests the conditions in the sequence we specify. If it finds the first logical test to be false, it moves on to the next logical test.
Step 2: The next step is to add the logical test and value if True for the Merit category. For this we say if the number is equal to 50 or is less than 70. Because we have already defined Fail as any number below 50, the formula won’t consider such a number as Merit even though our formula states any number below 70.
Step 3: Our final step is to define the logical test and value if True for the Distinction category. To do this, we have chosen numbers equal to 70 or numbers that are below 100.
Copy and paste the formula into the cells below with Ctrl + C and Ctrl + V respectively and you are done:
OR and SUMIF Function Workaround
Why is the section labelled as a workaround? Well, it is because the OR function won’t work as part of the singe criteria for the SUMIF Function, however, there is a workaround whereby we can check whether the range meets at least one of two or more conditions.
We would illustrate with a data set that shows the sales value of the various regions a company trades in. We created a section to add up sales values in two regions namely Africa and Asia.
To get a sum of the values that meet both criteria, we first need to write a SUMIF function that can check for multiple conditions, and we do that by using the formula below. Notice that we are using the curly brackets “{}” as an alternative for the OR function.
You would notice however that the formula returns an array as the result. The values for Africa and Asia are separately reported right next to each other, but we need both values to be added up.
To add up both values, we only need to enclose the previous formula in a SUM function and khalas, we’re done.
