COUNTIF within FILTER in Google Sheets formula

Question

I'm attempting to create a search sheet for my data where I have four columns that can contain values in any order and are all optional. For example:

Name	Gender	Value 1	Value 2	Value 3	Value 4
Alice	F	A
Bob	M	A	B	C
Charlie	F	C	Z
Charlie	M	B	D	A

My search options at the moment are name, gender, and values. All are optional except the name. I have implemented the name, gender, and first value at the moment using the following formula:

=FILTER(
  Data!D2:L,
  Data!D2:D = $B$1,
  IF(
    NOT(ISBLANK($D$1)),
    Data!E2:E = $D$1,
    Data!E2:E <> $D$1
  ),
  IF(
    NOT(ISBLANK($F$1)),
    (Data!I2:I = $F$1) + (Data!J2:J = $F$1) + (Data!K2:K = $F$1) + (Data!L2:L = $F$1),
    Data!D2:D = $B$1
  )
)

For additional context, my dropdown for the name is in $B$1, the dropdown for gender is in $D$1, and the dropdowns for values are in fields $F$1:$I$1.

My filter currently works, however, I require an IF statement for each value to see if it's blank first, and then joining each possible column's check. Lastly, if the dropdown for value 1 is blank, I can't simply return TRUE so I have to use a check I already know is true.

This is all well and good though I would have liked to use COUNTIF. Replacing the individual check for each column with:

COUNTIF(Data!I2:L, $F$1) > 0

Sadly, that doesn't appear to work and will throw the error, "FILTER has mismatched range sizes. Expected row count: 1999, column count: 1. Actual row count: 1, column count: 1."

Is there any way around this? Additionally, is there an easier way to approach this than to have an IF for each possibly blank field?

Just for some additional context, here's the full formula after I added in the 2nd through 4th values:

=FILTER(
  Data!D2:L,
  Data!D2:D = $B$1,
  IF(
    NOT(ISBLANK($D$1)),
    Data!E2:E = $D$1,
    Data!E2:E <> $D$1
  ),
  IF(
    NOT(ISBLANK($F$1)),
    (Data!I2:I = $F$1) + (Data!J2:J = $F$1) + (Data!K2:K = $F$1) + (Data!L2:L = $F$1),
    Data!D2:D = $B$1
  ),
  IF(
    NOT(ISBLANK($G$1)),
    (Data!I2:I = $G$1) + (Data!J2:J = $G$1) + (Data!K2:K = $G$1) + (Data!L2:L = $G$1),
    Data!D2:D = $B$1
  ),
  IF(
    NOT(ISBLANK($H$1)),
    (Data!I2:I = $H$1) + (Data!J2:J = $H$1) + (Data!K2:K = $H$1) + (Data!L2:L = $H$1),
    Data!D2:D = $B$1
  ),
  IF(
    NOT(ISBLANK($I$1)),
    (Data!I2:I = $I$1) + (Data!J2:J = $I$1) + (Data!K2:K = $I$1) + (Data!L2:L = $I$1),
    Data!D2:D = $B$1
  )
)

Answer 1

Assuming each value in any given row does not occur more than once, you could try the following formula:

=FILTER(A3:F,IF(B1="",ROW(A3:A),A3:A=B1),
             IF(D1="",ROW(A3:A),B3:B=D1),
             BYROW(C3:F,LAMBDA(vals,SUM(COUNTIF(vals,F1:I1))=COUNTA(F1:I1))))

Answer 2

Here's a slight alteration to what you already have for the IF-ELSE TRUE CHECK:

=filter(Data!D2:L,(Data!D2:D=B1)*
                   (if(len(E1),Data!E2:E=E1,1))*
                   (if(len(F1),((Data!I2:I=F1)+(Data!J2:J=F1)+(Data!K2:K=F1)+(Data!L2:L=F1)),1))*
                   (if(len(G1),((Data!I2:I=G1)+(Data!J2:J=G1)+(Data!K2:K=G1)+(Data!L2:L=G1)),1))*
                   (if(len(H1),((Data!I2:I=H1)+(Data!J2:J=H1)+(Data!K2:K=H1)+(Data!L2:L=H1)),1))*
                   (if(len(I1),((Data!I2:I=I1)+(Data!J2:J=I1)+(Data!K2:K=I1)+(Data!L2:L=I1)),1)))