When testing for multiple conditions, OR logic is used to determine if ANY condition in the test is TRUE.

The only time OR function evaluates to FALSE is if all conditions in a test are false

=OR( 2>5, 6>5 ) will evaluate to TRUE though the first logical test (2>5) is FALSE

=OR( 2>5, 5>6 ) will evaluate to FALSE because the two conditions are FALSE

Interestingly, the OR function can be represented by the plus (+) Sign

=(2>5) + (6>5) =TRUE

= (2>5) + (5>6 ) =FALSE

How is this possible?

In Excel, the numeric equivalent of a FALSE Boolean value is Zero (0) while any other number (positive or negative) is equivalent to a TRUE Boolean Value.

Therefore, =(2>5) + (6>5) =TRUE translates to 0 + 1 =1

And =(2>5) + (5>6) =TRUE translates to 0 + 0 =0

The one major difference between the plus (+) symbol and OR function is that OR function is not able to Spill logically!

See the examples below for a better explanation.

๐…๐ˆ๐‹๐“๐„๐‘ ๐…๐ฎ๐ง๐œ๐ญ๐ข๐จ๐ง ๐ฎ๐ฌ๐ข๐ง๐  ๐Ž๐‘ ๐‹๐จ๐ ๐ข๐œ

The FILTER function is one of the new dynamic array functions that have spill capability.

Spill means that a formula has resulted in multiple values, and those values have been placed in the neighboring cells.

In the below example, filter budget data for the year 2021 or 2022

=FILTER(Budget,OR(Budget[Year]=F2,Budget[Year]=F1)) results into an error #VALUE

=FILTER(Budget,(Budget[Year]=F2)+(Budget[Year]=F1)) results in the correct filtered data

To make OR function work with the FILTER function, you have to use the LAMBDA helper columns

=FILTER(Budget,
    MAP(Budget[Year],
       LAMBDA(a,OR(a=F2,a=F1))
      ))

IF ๐…๐ฎ๐ง๐œ๐ญ๐ข๐จ๐ง ๐ฎ๐ฌ๐ข๐ง๐  ๐Ž๐‘ ๐‹๐จ๐ ๐ข๐œ

Just like the FILTER function above, the plus (+) symbol works if you select an array of data but unlike the FILTER function, the OR function does not result in an error but incorrect results.

For example, Adjust the budget by 10% for all positions in the year 2022 or 2021

SUMIFS ๐…๐ฎ๐ง๐œ๐ญ๐ข๐จ๐ง ๐ฎ๐ฌ๐ข๐ง๐  ๐Ž๐‘ ๐‹๐จ๐ ๐ข๐œ

You cannot use OR function with SUMIFS but you can use the plus (+) sign.

By default conditions in SUMIFS follow the AND logic.

Here is how to use the OR logic

=SUMIFS(Budget3[Expenses],Budget3[Year],F2)+
SUMIFS(Budget3[Expenses],Budget3[Year],F1)

Alternatively

=SUM(SUMIFS(Budget3[Expenses],Budget3[Year],F1:F2))

CONCLUSION

If you will ever want to test if ANY condition in a set is TRUE, then OR logic is what you require.

The only other question to ask yourself is, “Do you want your results to spill or not?”

If you need Spilling results, Use the plus (+) symbol else use the OR function.

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.