Forum Discussion
Excel dynamic Spill Arrays
I tried using filter function inside countif but I keep getting error in formula. If I do the filter separate it returns the correct array and I can use the countif on the spilled array using the # ...
Counta does work but does not fit my circumstances. I don't know why you can't use a filter inside a countif function. Again you can use the countif against the result of filter spilled array but it doesn't work if you put it inside the countif.
Thanks,
Chuck
Chuck, COUNTIF expects range as first parameter, not formula. If, for example, you have something like
=COUNTIF(F2#,">10")spill is considered as range and it works. But if to use the formula instead it considered as incorrect formula. You may use
=SUMPRODUCT(--(F2#>10))and here you may use formula which generates the spill instead of it.