CODEWITHSUNDEEP
excel
Jack
Jack

Reputation: 723

AND doesn't work in an IF statement embedded in an array formula

I have an array formula in a cell. This formula displays the line number of a cell at the condition that the cell contains "FAIL" (and displays the line numbers with a coma separator). I wanted to add another condition to this formula so I tried to use AND but it now doesn't work anymore and I don't know why.

The original IF that worked looks like that:

IF(Y23:Z6000="FAIL";ROW(Y23:Z6000);"")

And what I want to write is

IF(AND(Y23:Z6000="FAIL";NOT(ISBLANK(A23:A6000));ROW(Y23:Z6000);"")

I have also tried with A23:A6000<>"" but same outcome.

The whole formula is this one: {=TEXTJOIN(",";TRUE;IF(AND(Y23:Z6000="FAIL";NOT(ISBLANK(A23:A6000)));ROW(Y23:Z6000);""))}

Can you please help me figure out what I did wrong? Thanks in advance.

Upvotes: 3

Views: 439

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Array formulas do not like AND or OR Either nest IFs or use * or + respectively:

=TEXTJOIN(",";TRUE;IF((Y23:Z6000="FAIL")*(NOT(ISBLANK(A23:A6000)));ROW(Y23:Z6000);""))

Array formulas require confirmation with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Upvotes: 4

Related Questions