formulasgoogle-sheets-query
I'm trying to copy cells B to F to another sheet if the text in cell J contains specific text. As an example case, I'm using the query function as:
=query('Sheet 1'!B1:J,"Select * Where J contains 'Apples'")
and that works fine, but I don't want all cells B to J to be copied across, just cells B to F.
Indeed, one cannot put things like "A6" inside the query string because then it would just a part of a string, not a cell reference. Instead, concatenate the value of the cell to the string:
=JOIN(", ", UNIQUE(QUERY(A2:F4, "select E where day(A) ="&day(A6))))
You don't need transpose, because join works equally well with one-dimensional arrays of either direction: vertical or horizontal.
That said, I find a solution using filter slightly more readable:
=join(", ", unique(filter(E$2:E$4, A$2:A$4 = A6)))
Assuming your data is in N2:Q5, and the list of acceptable Stages in S2:S3, the formula below will return what you want.
=rows(filter(N2:Q5, not(isna(match(Q2:Q5, S2:S3, 0)))))
Best Answer
You mention: