Google-sheets – Perform operation on specific column in filtered range, right after filtering

formulasgoogle sheetsgoogle-sheets-arrayformula

I have this formula in E2: =FILTER(A1:C20,B1:B20=2) and need to perform substitute like:

I'm hoping to do the formula in a single cell, without additional columns.

=ARRAYFORMULA(FILTER({A1:B, SUBSTITUTE(C1:C, "stuff", "blah")}, B1:B=2))

Use JOIN subject to FILTER by ISBLANK. For the example spreadsheet below, the command

=JOIN(", ", FILTER(A1:A6, ISBLANK(B1:B6)))

returns "Robert, Jay, Tim"

      A          B
1    Jon      attacked   
2    Robert     
3    Grace    attacked  
4    Jay        
5    Tim        
6    Shog     attacked

The formula

 =MAX(FILTER(A:A, B:B="something"))

gives the maximum of the entries in the A column for which the corresponding entry in B column is "something". See documentation for FILTER.