Knowing how to use `IF`

within the `FILTER`

function in Google Sheets** is useful if you would want to skip filtering a table if the condition is blank.**

##### Table of Contents

- The Anatomy of the Single Condition IF and FILTER Combination in Google Sheets
- A Real Example of the Single Condition IF and FILTER Combination in Google Sheets
- How to Use the Single Condition IF and FILTER Combination in Google Sheets
- The Anatomy of the Multiple Conditions IF and FILTER Combination in Google Sheets
- A Real Example of the Multiple Conditions IF and FILTER Combination in Google Sheets
- How to Use the Multiple Conditions IF and FILTER Combination in Google Sheets

Normally, when you write the `FILTER`

formula with the blank criterion, it would filter rows containing blank cells in the corresponding column.

Let’s take an example!

Say you have a list of products, with their categories and prices. However, not all of the products have their price listed 🍎🍍🥔

Let’s now say that our criterion is in cell **D1** and it is **5**. If you write the `FILTER`

formula like this **=FILTER(A2:C11,C2:C11<=D1)**, where **C** is the column with the prices, it would filter rows with the price lower than or equal to our criterion (in cell **D1**), which is **5**. If we remove the criterion from the cell **D1**, the `FILTER`

formula would filter rows with blank cells in column **C**. But let’s say we want the entire table as the filtered output if the criterion is blank.

**So how do we do that?**

Easy. This is where we will use the `IF`

statement within the `FILTER`

Function in Google Sheets.

Let’s first take a look at the anatomy of the single condition `IF`

and `FILTER`

combination to help you better understand how to use IF within the FILTER function in Google Sheets.

## The Anatomy of the Single Condition IF and FILTER Combination in Google Sheets

The syntax (the way we write) the `FILTER`

function is as follows:

Let’s break this down and explain each of these terms.

**=** the equals sign is the sign we put at the beginning of any function in Google Sheets.

**FILTER()** is our function. We will have to add the following variables into it for it to work.

**range** is the data to be sorted.

**condition1** is a column or row containing **TRUE** or **FALSE** values corresponding to a selected column or row of the range. The function must have at least one condition.

**condition2, …** are additional conditions you can add if you need to check more than one condition.

In this guide, our condition is the `IF`

function. The syntax (the way we write) the `IF`

function is as follows:

Let’s break this one down, too!

**=** as said before, the equals sign is used at the beginning of any function in Google Sheets.

**IF()** this is our IF function. To make it work, we will have to add our logical_expression, value_if_true, and value_if_false inside the round brackets.

**logical_expression** is the condition that we want to test to see whether it is true or false.

**value_if_true** is the operation (the work to be done) that we want to carry out if the logical_expression is tested to be true

**value_if_false** is the operation (the work to be done) that we want to carry out if the logical_expression is tested to be false.

When we have a single condition `IF`

and `FILTER`

combination, our formula will look like this:

## A Real Example of the Single Condition IF and FILTER Combination in Google Sheets

Take a look at the example below to see how the single condition `IF`

and `FILTER`

combination is used in Google Sheets.

We have a list of products (in column **A**), with their categories (in column **B**) and prices (in column **C**). As we can see, some products do not have their price listed.

Let’s say that we want to filter the products with prices lower than or equal to **5**. Our criterion (**5**) is in cell **D1**, so our formula will look like this **=FILTER(A2:C11,C2:C11<=D1)**. We will paste this formula in cell **E2** and it will filter the rows with the price (in column** C**) lower than or equal to **5**.

But what will happen if we remove the criterion from the cell **D1**?

As you can see, the formula would filter the rows with blank cells in column **C**.

But what if now we want the entire table as the filtered output if the criterion is blank? This is where we will use the single condition `IF`

and `FILTER`

combination.

We will enter the following formula **=FILTER(A2:C11,IF(D1=””, n(C2:C11)<>””, C2:C11<=D1))** in cell **E2** and it would filter the entire table instead of just rows with blank cells in column **C**.

## How to Use the Single Condition IF and FILTER Combination in Google Sheets

Let’s begin writing your own single condition `IF`

and `FILTER`

combination in Google Sheets, step-by-step.

- First, make sure that the area where you would want to put your filtered data is empty. If so, click on a cell where you would enter the formula to make it active. This time, we will use the cell
**E2**.

- Start the function with the equals sign
**‘=’**and type the name of the function, which is**FILTER**. As you start typing, you will get auto-suggestions with functions that start with the same letters. Choose the`FILTER`

function from the suggestions or continue typing.

- After the opening round bracket
**‘(‘**we will enter our range, which is**A2:C11**. Put a comma**‘,’**after it.

- Now we should enter our other function, which is
**IF**.

- Enter the opening round bracket
**‘(‘**and our**logical_expression**, which is**D1=””**. Put a comma**‘,’**after it.

- Now we should enter our
**value_if_true**which is**n(C2:C11)<>””**meaning that if cell**D1**is empty, the formula should filter all the rows in range**C2:C11**which are lower or higher than cell**D1**. Put another comma**‘,’**.

- And finally, we should enter the
**value_if_false**which is**C2:C11<=D1**meaning that if cell**D1**is not empty, the formula should filter all the rows in range**C2:C11**which are lower or equal to cell**D1**. Enter two closing round brackets**‘))’**to close the function and hit the**Enter**key on your keyboard.

- If you did everything right, the formula would filter the entire table
**(A2:C11)**since the cell**D1**is empty.

That’s it! Now you know how to use the single condition `IF`

and `FILTER`

combination in Google Sheets!

You can make a copy of the spreadsheet using the link below and practice some more before continuing to the multiple conditions `IF`

and `FILTER`

combination in Google Sheets:

## The Anatomy of the Multiple Conditions IF and FILTER Combination in Google Sheets

The syntax (the way we write) the formula for multiple conditions `IF`

and `FILTER`

combination is similar to the one with the single condition, and is as follows:

As you can see, the only difference is that now we have two conditions.

## A Real Example of the Multiple Conditions IF and FILTER Combination in Google Sheets

Let’s go back to our products! 🍎🍍🥔

Once again, we will filter our products by the price but this time we will also filter them by category. As you can see, we will look for all the **‘Vegetables’** (cell **D2**) that are priced lower than or equal to **‘5’** (cell **D1**).

But what if the cells **D1** and **D2** were blank? The above formula would filter the entire table. Let’s take a look at how to do this!

## How to Use the Multiple Conditions IF and FILTER Combination in Google Sheets

The first few steps are the same as the ones for the single condition `IF`

and `FILTER`

combination in Google Sheets. Just instead of two closing round brackets **‘)’** this time we will enter just one, and add another condition. The second condition is **IF(D1=“”,n(C2:C11)<>“”,C2:C11<=D1****) **so our formula will look like this **=FILTER(A2:C11,IF(D1=“”,n(C2:C11)<>“”,C2:C11<=D1),IF(D2=“”,n(B2:B11)<>“”,B2:B11=D2))**.

If you did everything right, the formula would filter the entire table **(A2:C11)** since both cells, **D1** and **D2** are empty.

That’s it! Now you know how to use the multiple conditions `IF`

and `FILTER`

combination in Google Sheets, as well!

Take a look at the **other Google Sheets formulas** you can use to create even more effective formulas! 🙂

## 1 comment

Helpful, Thanks!