'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 |
|---|
