Google sheets filter multiple columns using OR but returns AND result-CodePudding

The formula I have tried is as seen in the image. =filter(A:A,(month(B:B)=11) (month(C:C)=11) )

Anyone knows why this is returning the AND result instead of OR?

CodePudding user response：

Couple of things:

The conditions need to evaluate to true or false
" " is numerical add not Boolean "AND"
It appears you want either (OR):
- B is 11th month and C is not 11th month
- B is not 11th month and C is 11th month

So something like (I've not tried it):

=filter(A:A, 
  OR(
    AND(
      MONTH(B:B)=11,
      NOT(MONTH(C:C)=11)
    ),
    AND(
      NOT(MONTH(B:B)=11),
      MONTH(C:C)=11
    )
  )
)

CodePudding user response：

It is not returning AND but an error in MONTH(non-date) makes the matching fails.

=filter(A:A,(IFERROR(month(B:B),)=11)   (IFERROR(month(C:C),)=11) )