Forum Discussion
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(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!