Google Sheets: Conditional Formatting with Multiple Conditions

When we think about multiple conditions, the first thing that comes to mind is its logical formatting. So it comes to no surprise that when creating custom formulas for conditional formatting with multiple conditions in Google Sheets, we primarily use the OR and AND logical operations.

3 Ways to Use Conditional Formatting with Multiple Conditions in Google Sheets

1. Using OR Logic

We start with the OR logical condition. The logic states that, even if one condition is TRUE, the whole formula will be considered as a TRUE statement.

For our example, we will highlight the Names of the employees that have a Sales Number under 50 in at least one Product from the following dataset.

dataset for conditional formatting with multiple conditions in google sheets

The simplest way to use the OR logic is by using the addition operator (+) in between the conditions. So, in the custom formula field, we enter the formula:

=(B4<50)+(C4<50)+(D4<50)

If you want to know how to apply custom formulas in conditional formatting, please visit our Using Conditional Formatting With Custom Formula in Google Sheets article.

using or logic with addition operator for conditional formatting

Condition Breakdown:

  • Our data range that was chosen is only the Salesperson column (A4:A13) to highlight only names. See Apply to range section in the image.
  • Each condition corresponds to a Product column, so we have three conditions for three columns.

Alternatively, we can also use the OR function to get the same result:

=OR(B4<50,C4<50,D4<50)

conditional formatting with multiple conditions in google sheets using or function

On top of being concise, the OR function allows users to use it in various formulas which we will see later in this article.

That concludes the fundamental way to use the OR logic to use conditional formatting with multiple conditions in Google Sheets

OR Alternative: Add Another Rule Option

The conditional formatting window of Google Sheets allows us to add more conditions to our selected data range. While the uses may be limited, it still mimics much of the OR logic that we have just discussed.

Let’s say we want to highlight all the marks under 60 with one color and all the marks above 90 with another from the following table.

dataset for and logic

These are essentially two separate conditions that can be applied directly from the Conditional format rules window.

First of all, let’s select the range of formatting. Since we only want to highlight the marks our data range will be B4:D13.

Then we add our first condition as a custom formula to highlight all the marks under 60.

=B4<60

first condition highlighting

The Formatting style will be of your choice.

Now, notice the Add another rule option at the bottom of the window.

add another rule option

Clicking on it will take us to a fresh window with most of the conditions the same as before. Add the second condition for our task, that is, highlighting all the marks greater than 90 but with a different color.

=B4>90

traditional conditional formatting with multiple condition in google sheets

This process is essentially applying two different conditions on the same range in conditional formatting in Google Sheets. Just make sure that the condition range does not overlap.

2. Using AND Logic

The AND logic follows similar syntaxes as the OR function, only this time, instead of the plus operator (+) we will use the multiplication operator or asterisk (*).

For a refresher: The AND logic will return TRUE only if all the conditions are TRUE.

So, coming back to our initial dataset, we will apply the conditional formatting condition to highlight the names that have achieved more than 100 sales in all three products.

dataset for conditional formatting with multiple conditions in google sheets

Our first AND logic formula:

=(B4>100)*(C4>100)*(D4>100)

conditional formatting with and logic

And of course, there is an AND function alternative:

=AND(B4>100,C4>100,D4>100)

conditional formatting with multiple conditions using and function

AND-OR Combination

The main advantage of using functions is the ability to combine them to extract more complex results. That is what we are going to do with the AND and OR functions.

For this example, we will use conditional formatting to highlight the rows that have an Amount Sold value of over 300 units and are in the Location of New York or Philadelphia from the following dataset.

dataset of name, amount sold and location

Our custom combined formula for conditional formatting is:

=AND($B3>300,OR($C3="New York",$C3="Philadelphia"))

using and or combination on conditional formatting with multiple conditions in google sheets

Notice that we have used all the value cells from our table for our formatting range, A3:C22.

3. Using the COUNTIFS Function (Situational)

The situation is highlighting duplicates in Google Sheets, but this time with multiple conditions.

Generally, we use the COUNTIF function to highlight duplicates in a single column, however, for multiple conditions or columns, we have to utilize the COUNTIFS function.

Let’s keep things simple. From the following dataset, we will highlight all the rows where both columns have duplicate entries.

dataset for countifs

Our formula:

=COUNTIFS($B$3:$B$13,$B3,$C$3:$C$13,$C3)>1

using countifs function for multiple column conditions in conditional formatting

Final Words

That concludes all the ways we can use conditional formatting with multiple conditions in Google Sheets. We hope that the methods we have discussed come in handy for your spreadsheet tasks.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo