Excel COUNTIF with Filtered Fomula as the Range Parameter

Question

Hi,&nbsp;I'm trying to get a COUNTIF formula to work where the Range Parameter is a formula that returns a Dynamic Range.&nbsp;The following fomula entered in Cell K1 returns a dynamic array=UNIQUE(FILTER($B$1:$B$30,($A$1:$A$30="GROUP")*($C$1:$C$30=TRUE)))&nbsp;The following formula references it (K1#), and it works as expected=FILTER($G$1:$H$60,COUNTIF(K1#,$F$1:$F$60)=1)&nbsp;If I replace the K1# reference in this formula with the formula that returns the dynamic array, it gives me the 'There is a problem with your formula' error=FILTER($G$1:$H$60,COUNTIF(UNIQUE(FILTER($B$1:$B$30,($A$1:$A$30="GROUP")*($C$1:$C$30=TRUE))),$F$1:$F$60)=1)&nbsp;I guess this is something to do with a Dynamic array formula being nested in another dynamic array formula?&nbsp;Does anyone have another solution if this is not possible?&nbsp;I've uploaded a read only copy of the spreadsheet is here - Excel COUNTIF FILTER.xlsx&nbsp;Any help would be much appreciated&nbsp;Thanks!

riny_van_eekelen · Accepted Answer

CrankyPants2382&nbsp;It's more that COUNTIF doesn't work with dynamic arrays I believe.&nbsp;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))
        )
    )
)-&nbsp;

Forum Discussion

Excel COUNTIF with Filtered Fomula as the Range Parameter

Share

Resources