Excel COUNTIF with Filtered Fomula as the Range Parameter

[CrankyPants2382]

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(FILTER($B$1:$B$30,($A$1:$A$30="GROUP")*($C$1:$C$30=TRUE)))

The following formula references it (K1#), and it works as expected

=FILTER($G$1:$H$60,COUNTIF(K1#,$F$1:$F$60)=1)

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)

I guess this is something to do with a Dynamic array formula being nested in another dynamic array formula?

Does anyone have another solution if this is not possible?

I've uploaded a read only copy of the spreadsheet is here - Excel COUNTIF FILTER.xlsx

Any help would be much appreciated

Thanks!

[#2 · Accepted solution]

@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))
        )
    )
)

-

[#3]

COUNTIF/SUMIF/COUNTIFS/SUMIFS, etc - only accept ranges. No dynamic arrays.