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(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?

Any help would be much appreciated

Thanks!

2 Replies
best response confirmed by CrankyPants2382 (Copper Contributor)
Solution

# Re: Excel COUNTIF with Filtered Fomula as the Range Parameter

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

-

# Re: Excel COUNTIF with Filtered Fomula as the Range Parameter

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