Forum Discussion

Copper Contributor

Apr 17, 2020

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 # ...

Formulas and Functions

Silver Contributor

Apr 17, 2020

Try something like this:

=COUNTA(FILTER(E6:E8,E6:E8="Red"))

Copper Contributor

Apr 17, 2020

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

SergeiBaklan
Diamond Contributor
Apr 17, 2020
Chuck835

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.
- Chuck835
  Copper Contributor
  Apr 17, 2020
  SergeiBaklan
  
  Sergei,
  
  Thanks for your response, I get it....It still doesn't make sense that it doesn't work bc the inner filter function would be interpreted first producing the spilled array, then the countif would be evaluated. Oh well....
  
  Thanks again for the explanation,
  Chuck
  - Detlef_Lewin
    Silver Contributor
    Apr 17, 2020
    Chuck835
    It has nothing to do with dynamic arrays but with COUNTIF() expecting a range/reference.
    A range is not the same as an array!
    A range can refer to a single cell like G4 or any expression with a reference operator or using functions like INDIRECT(), OFFSET() and probably one or two more.

Resources