能否在FILTER函数中嵌套IF函数？现有筛选公式匹配失败求助

Hey there! Let's tackle this FILTER function issue you're having, and answer your question first: yes, you absolutely can nest IF functions inside FILTER—your problem is just with how you structured the condition.

What Was Wrong With Your Original Formula? #

Your attempt used & to concatenate conditions, which converts boolean values (TRUE/FALSE) into text strings. FILTER relies on boolean arrays (arrays of TRUE/FALSE) to filter rows, so this text concatenation broke the logic entirely, leading to no matching results.

Correct Approaches to Dynamic Filtering #

Here are two clean ways to achieve what you want:

Method 1: Using IF to Return a Dynamic Boolean Array

This approach uses IF to either apply the P-column filter or return a full array of TRUE (which acts as a "no filter" condition):

=FILTER('All Jobs'!A2:P,
  'All Jobs'!B2:B>=B1,
  'All Jobs'!B2:B<=D1,
  'All Jobs'!G2:G=F1,
  IF(OR(G1="Open", G1="Closed"), 'All Jobs'!P2:P=G1, TRUE)
)

• When G1 is "Open" or "Closed", the final condition checks if column P matches G1.
• When G1 is "All", the final condition returns TRUE for every row, so it doesn't filter anything out.

Method 2: Using Logical OR for a Concise Condition

If you prefer a more compact formula, you can combine all conditions into a single boolean array with multiplication (which acts as AND logic) and include the dynamic check directly:

=FILTER('All Jobs'!A2:P,
  ('All Jobs'!B2:B>=B1)*
  ('All Jobs'!B2:B<=D1)*
  ('All Jobs'!G2:G=F1)*
  (OR(G1="All", 'All Jobs'!P2:P=G1))
)

• The OR(G1="All", 'All Jobs'!P2:P=G1) part means: either G1 is "All" (so this condition is always TRUE), or column P must match G1.
• Multiplying the conditions together ensures all of them need to be TRUE for a row to be included.

Both methods will work reliably—pick the one that makes the most sense to you!

内容的提问来源于stack exchange，提问作者Norvin