Excel COUNTIF with Filtered Fomula as the Range Parameter

Copper Contributor



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



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



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



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



2 Replies
best response confirmed by CrankyPants2382 (Copper Contributor)

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

    grpselect, TRANSPOSE(
                    $A$1:$A$30 =
                ) *
                        $C$1:$C$30 =
            $F$1:$F$60 = grpselect,
            LAMBDA(arr, OR(arr))



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