Forum Discussion

CrankyPants2382's avatar
CrankyPants2382
Copper Contributor
Jul 09, 2023
Solved

Excel COUNTIF with Filtered Fomula as the Range Parameter

Hi,   I'm trying to get a COUNTIF formula to work where the Range Parameter is a formula that returns a Dynamic Range.   The following fomula entered in Cell K1 returns a dynamic array =UNIQUE(F...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jul 10, 2023

    CrankyPants2382 It's more that COUNTIF doesn't work with dynamic arrays I believe.

     

    In the attached file I've composed an all-in-one formula that does what you need, though I'm convinced that there are other (perhaps) better ways to do it. Just can't think of such a solution right now. In R1 I used this formula:

    =LET(
    grpselect, TRANSPOSE(
        UNIQUE(
            FILTER(
                $B$1:$B$30,
                (
                    $A$1:$A$30 =
                        "GROUP"
                ) *
                    (
                        $C$1:$C$30 =
                            TRUE
                    )
            )
        )
    ),
    FILTER(
        $G$1:$H$60,
        BYROW(
            $F$1:$F$60 = grpselect,
            LAMBDA(arr, OR(arr))
        )
    )
)

    -

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jul 10, 2023

CrankyPants2382 It's more that COUNTIF doesn't work with dynamic arrays I believe.

 

In the attached file I've composed an all-in-one formula that does what you need, though I'm convinced that there are other (perhaps) better ways to do it. Just can't think of such a solution right now. In R1 I used this formula:

=LET(
    grpselect, TRANSPOSE(
        UNIQUE(
            FILTER(
                $B$1:$B$30,
                (
                    $A$1:$A$30 =
                        "GROUP"
                ) *
                    (
                        $C$1:$C$30 =
                            TRUE
                    )
            )
        )
    ),
    FILTER(
        $G$1:$H$60,
        BYROW(
            $F$1:$F$60 = grpselect,
            LAMBDA(arr, OR(arr))
        )
    )
)

-

 

Resources