Forum Discussion

Tin Contributor

Aug 29, 2026

Advanced Filter

Hello. I created a filter that has 2 conditions. 1. If the Ref Cert or the SK Cert is YES, bit should appear. The problem is that if the columns with Ref Exp or SK Exp is blank, then it gives me ...

Formulas and Functions

Olufemi7

Steel Contributor

Sep 02, 2026

Hello BaltaD,

The FILTER criteria is fine. The issue is the &IF(ISBLANK(I3),"",I3) at the end.

The & converts the result to text, so Excel displays date serial numbers such as 47633. Also, I3 refers only to the first row rather than the corresponding Ref Exp value for each filtered row.

Try:

=FILTER(C3:N500,(H3:H500="YES")+(J3:J500="YES"),"")

Then format the Ref Exp and SK Exp columns as Date.

You can also check the source dates with =ISNUMBER(I3). If it returns TRUE, the value is a valid Excel date.

PeterBartholomew1

Silver Contributor

Sep 04, 2026

= LET(
    include?, MAP(refCert="YES", skCert="YES", AND),
    nonBlank, IF(ISBLANK(table), "", table),
    FILTER(nonBlank, include?)
  )

As suggested by m_tarler , LET allows a formula to developed in readable steps without relying upon 'old-school' tricks such as concatenation. The first line builds the criterion for selection (MAP is needed to ensure the AND function is applied row by row). The next replaces blanks by empty strings across the entire table. The FILTER should then be quite readable, especially so because defined names have been used in place of direct cell references throughout.