CODEWITHSUNDEEP
google-sheets
Masadow
Masadow

Reputation: 782

How to use a function as a criteria in COUNTIF

I have a bunch of columns that holds scores like 3-1 1-4 1-0 2-2.

I would like to count all columns that are winning, ie. left number is higher than right one.

I have this formula to know if a column is winning : =LEFT(H2; FIND("-"; H2)-1)-RIGHT(H2; FIND("-"; H2)-1)

What I want to do now is to use this formula within COUNTIF, something along the line :

=COUNTIF(A1:A10; "LEFT(CURRENT_COLUMN; FIND("-"; CURRENT_COLUMN)-1)-RIGHT(CURRENT_COLUMN; FIND("-"; CURRENT_COLUMN)-1)")

Is there any way I can do it with a single formula ?

Upvotes: 1

Views: 105

Answers (1)

user3717023
user3717023

Reputation:

Complex filtering like this can be done with filter, and then the results can be counted with counta. Example:

=counta(filter(A:A, LEFT(A:A, FIND("-", A:A)-1) > RIGHT(A:A, FIND("-", A:A)-1)))

The first argument of filter is the range to be filtered; the second is a formula based on that range (or another range with the same row count) which returns True or False. The rows where the formula evaluates to True are returned.

Upvotes: 1

Related Questions