IF Statement within FILTER Function in Google Sheets

Why would you ever need to use an IF statement within the FILTER function in Google Sheets?

That’s exactly what I’ll walk you through in this post—starting with the simple reason:
Sometimes you just want to skip filtering when the condition is blank.

Let me explain.

Table of Contents

What Usually Happens When the Filter Condition Is Blank?

Normally, if you write a FILTER formula and the criteria cell (say, G1) is empty, Google Sheets won’t return the full table. Instead, it filters for rows with blank values in the relevant column—which isn’t what you want most of the time.

So what do you do when you want the whole table to show until a condition is entered?

Let’s look at the usual workaround—using IF outside the FILTER function—and then move on to a better option: using IF inside the FILTER.

IF Statement Outside the FILTER Function in Google Sheets

Say your data is in Sheet1!A1:E, and you’ve got a dropdown or text input in G1 to filter by company name.

Sample Data

Company	Cost Center	Q1	Q2	Q3
Company 1	101	1.5	1.6	2
Company 1	102	1.5	1.5	1.75
Company 2	103	2	2.5	2
Company 2	104	2	2	2.25
Company 3	105	2.25	2	2.5
Company 3	106	2	2	2
Company 3	107	2.5	3	3
Company 4	101	2	2	2

You’d probably write something like:

=FILTER(A1:E, A1:A = G1)

Which works—until G1 is empty. Then you either get nothing or rows with blanks in column A.

To fix that, you can wrap it with IF:

=IF(G1 = "", {A1:E}, FILTER(A1:E, A1:A = G1))

This says: if there’s no condition, just return the full table.

GIF showing how an IF statement outside the FILTER function displays the full table when no condition is set in Google Sheets

Quick Tip:

Instead of {A1:E}, you could also use ARRAYFORMULA(A1:E). Either way, they both return the entire range.

Why Use IF Inside the FILTER Function?

This is where things get interesting.

If you have more than one condition, things get messier using IF outside FILTER. That’s why it’s much cleaner to put your IF logic inside the FILTER function itself.

And guess what? Even if you have just one condition, this approach is still worth learning.

Single Condition: IF and FILTER Combined in One Formula

Let’s start by rewriting the earlier formula using IF within the FILTER.

=FILTER(A1:E, IF(G1 = "", N(A1:A) <> "", A1:A = G1))

This does exactly what we want:

If G1 is empty → return all rows
If G1 has a value → filter rows where column A matches that value

How it works:

G1 = "" checks if the condition is empty
N(A1:A) <> "" ensures the formula still returns all non-blank rows
A1:A = G1 is the regular condition for filtering

Not sure why N() is used? It’s just a trick to return a numeric array with the same shape—similar to using ROW(A1:A). We just need something that isn’t blank.

Multiple Conditions: IF Inside FILTER for More Flexibility

Now let’s take it further.

Say you’ve got two filter criteria:

G1 for Company name
G2 for Cost Center

You want the formula to use whichever criteria is filled. If both are blank? Just show the full table.

Here’s how that looks:

=FILTER(A1:E, IF(G1 = "", N(A1:A) <> "", A1:A = G1), IF(G2 = "", N(B1:B) <> "", B1:B = G2))

Animated example of using an IF statement within the FILTER function in Google Sheets to apply multiple optional conditions

This formula is a more dynamic version of the standard two-condition filter:

=FILTER(A1:E, A1:A = G1, B1:B = G2)

How it works in real use

G1 (Company)	G2 (Cost Center)	What You Get
Company 1	101	Rows matching both conditions
Company 1	(blank)	Rows matching Company only
(blank)	101	Rows matching Cost Center only
(blank)	(blank)	Entire table (no filtering applied)

Pretty handy, right?

What If You Have More Than Two Conditions?

No problem—just keep adding IF blocks inside the FILTER. For example:

=FILTER(A1:E, IF(G1="",...,...), IF(G2="",...,...), IF(G3="",...,...))

Each condition can be optional, and the formula will just adapt.

Bonus Tip: Filter Multiple Values from the Same Column

Let’s say G1:G2 contains multiple company names (e.g., Company 1, Company 2), and you want to filter based on either.

You don’t need IF here. Just use this:

=FILTER(A1:E, REGEXMATCH(A1:A, TEXTJOIN("|", 1, G1:G2)))

This checks if any of the values in G1:G2 match column A. Perfect for multi-select filters.

Conclusion

The IF statement within the FILTER function in Google Sheets is one of those tricks that gives your formulas serious flexibility. It lets you:

Skip conditions that aren’t set
Handle multiple criteria cleanly
Avoid clunky outer IF wrappers

Once you get the hang of it, you’ll probably use it all the time.