'Excel apply functions on FILTER. ie COUNTIF(FILTER(RANGE,FilterCondition),IfCondition)

Excel doesn't seem to let you use function such as RANK/COUNTIF on a FILTER(...) formula without first having the output of the filter somewhere in the spreadsheet. Ie, functions take range as argument but not arrays.

Let's assume in Column A, I have number 1 to 10 on the ten first rows

and in column B a filter on even numbers, =FILTER(A1:A10,MOD(A1:A10,2)=0)

If I want to count the number of rows of array returned by the filter function, I can use =COUNT(B1#)

If I want to count only the values above 5 using COUNTIF, this will also work =COUNTIF(B1#, ">5")

Now, I would like to get rid of the intermediary B Column.

For the COUNT function, it works. =COUNT(FILTER(A1:A10,MOD(A1:A10,2)=0)) returns 5.

For the COUNTIF function, it doesn't work anymore. I assume this because it takes a range as a parameter and not an array.

Excel warns in a popup: "There's a problem with this formula".

Is there a workaround this without using a temporary column to store the array? It seems I can only use functions that takes an array as parameter on top of the filter function.

EDIT: I have used COUNTIF to build a simple example but I am more interested in other functions such has RANK. COUNTIF was not the best example because you can obviously move the IF conditions in the FILTER conditions. Thank you @JvdV



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source