CODEWITHSUNDEEP
excelfiltercountiftake
Juan Jose Collantes Antezana
Juan Jose Collantes Antezana

Reputation: 17

How to use COUNTIF and TAKE function in Excel

I'm new here.

I have a problem with an Excel function.

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

Image 1

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

Image 2

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.

Upvotes: 0

Views: 244

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34210

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

enter image description here

Upvotes: 0

Related Questions