How to use COUNTIF and TAKE function in Excel

Question

I'm new here.

I have a problem with an Excel function.

As the result of take(filter(...)) in Excel I have this list:

So, I want to apply COUNTIF there like: COUNTIF(TAKE(FILTER(..))>74). But when I've tried to do this, Excel show me this:

Does anyone know how to combine these function to have the result that I want (Excel counts how many cells have values greater than 74).

I want to combine countif, take and filter function in Excel.

Answer 1

Well having established that Countif doesn't work with arrays let's have a look at some alternatives. Let's assume that your base formula is something like:

=TAKE(FILTER(A1:A100,A1:A100<100),30)

(1) Count + Filter:

=LET(arr,TAKE(FILTER(A1:A100,A1:A100<100),30),COUNT(FILTER(arr,arr>74)))

Straightforward but a bit long. I have used Let to avoid repeating the whole of the base formula.

(2) Take reciprocal of condition and count non-error cells:

=COUNT(1/(TAKE(FILTER(A1:A100,A1:A100<100),30)>74))

OK but a bit hacky maybe.

(3) The one you will see most often - use Sum instead of Count:

=SUM(--(TAKE(FILTER(A1:A100,A1:A100<100),30)>74))

where the -- is necessary to convert true/false to 1/0.

(4) Use a lambda

=REDUCE(0,TAKE(FILTER(A1:A100,A1:A100<100),30),LAMBDA(a,c,a+(c>74)))

Also fine, maybe a bit verbose.

I might add timings to these at some future point but probably they will all be similar and most people would just use the third one, Sum(--arr).