How to use an AND statement with ARRAYFORMULA when searching a range in a Google Sheet-CodePudding

I have this function which works great.

=transpose
(
    split
    (
        ARRAYFORMULA
        (
            TEXTJOIN
            (
                ", ",TRUE,
                (
                    IF
                    (
                        A3:A100="Active",
                        B3:B100
                        ,""
                    )
                )
            )
        ),","
    )
)

It says if Anything Between A3 and A100 is the word Active, then Display B3 in a vertical list.

I am trying to query multiple parameters though and am hitting dead ends. When I try to add an AND statement to the IF query, I get no results. Example:

=transpose
(
    split
    (
        ARRAYFORMULA
        (
            TEXTJOIN
            (
                ", ",TRUE,
                (
                    IF
                    (
                        AND(A3:A100="Active", C3:C100="Sold"),
                        B3:B100
                        ,""
                    )
                )
            )
        ),","
    )
)

CodePudding user response：

not supported. use multiplication:

=TRANSPOSE(SPLIT(ARRAYFORMULA(TEXTJOIN(", ", 1, 
 IF((A3:A100="Active")*(C3:C100="Sold"), B3:B100, ))), ","))