Excel - Advanced Filter - AND for single column

[Makhaira]

Currently getting to grips with advanced filters. I know that I can do OR on a single column, or AND across multiple columns, but how can I do AND on a single column?

One of the criteria I want to put in place is to filter a specific Date column such that the Date is less than TODAY()+28 and the value isn't N/A.

Entering AND(<TODAY()+28,<>"N/A") doesn't work... (Tried sticking an equals on the front too, no change.

Any suggestions gratefully received! Thanks.

 

[Martynt74]

I assume the issue is that the N/A is the result of a formula? If so, entering <>"N/A" wouldn't work as that would be text.

Bit of a long way around but you could always have another column with the formula =IF(AND(ISERROR(A2)=FALSE,<TODAY()+28),"Include","Exclude"

And then filter where that equals Include

Obviously that assumes the data is in Cell A2

 

[Makhaira]

No, it was text. I've also tried using the =NA() formula and altering the check to reflect that.

Yea, the only option I came up with that worked was creating a helper column like that, it's just a bit messy. Thanks anyways!

 

[SourChipmunk]

I know this thread is a couple weeks old. I'm not clear on the question -- I'm not sure what your desired output is.

=IF(A2<TODAY()+28,"TRUE",IF(A2="N/A","NULL","FALSE"))

This will return a "TRUE" if the date is less than 28 days from today, "FALSE" if it is not, and "NULL" if it finds that N/A.

Edit: If you have a formula in cell A2 and it is returning the #N/A error, update the formula in A2 to check for the error.

=IFERROR([your formula],"N/A")

Where "N/A" in both instances can be whatever you want.

Is that anything like you're shooting for, or did I completely misunderstand?