Google Sheets Dynamic Condition in Filter Function

Question

I have a spreadsheet where I am amassing metadata and statistics based on filters. For each row of my summary spreadsheet, I want the full statistics of all of the data in my spreadsheet that is relevant to that filter.

Example Formatting

Datatype      |Filter         |Mean |Median...
All           |None           |15   |12
When C < 15   |(C2:C1000<15)  |12   |10

In order to save time, I'd like to be able to write my formulae for my statistics based on a condition I have pre-written in the "filter" column rather than have to paste it into each filter. Example of what I'm trying to do:

=COUNT(FILTER(A2:A1337,I2:I1337<C2:C1337,A1))

where A2:A1337 is my data, I2:I1337 < C2:C1337 is another condition, and A1 contains the plaintext condition of the filter I'd like to apply to this filter statement (so like the cell in the 3rd row, 2nd column in above example).

I believe I could also do what I want with queries or using tons of space by using multiple stages of filters and running stats on those new tables, but I'd really like to save myself space and trouble. Am I overlooking an easy way to "insert" plaintext as conditions into filter commands?

Answer 1

Not what you were looking for, but a temporary workaround is to hardcode your formula to first row, use autocomplete to fill all of your rows, then do a find and replace (with formula search on) on your condition for each row.

So start first row with filter(range, condition1)

drag that across many rows

find and replace condition1 with whatever you want.

Answer 2

Short answer

As an alternative of FILTER try QUERY

Explanation

FILTER doesn't allow the use of plain text as the criteria arguments. I know that is possible to convert the plain text references to references by using INDIRECT but I don't think that could be possible to do the same with comparison operators.

By the other hand, QUERY uses a string as argument.

If A1 has the value where C > 15 then the formula

=QUERY(Data!A:I,"select * "&A1)

The formula of the example should replaced by the following:

A1: I < C
A2: C > 15

=QUERY(Data!A:I,"select * where "&A1&" and "&A2)