I'm compiling stats for our Boom Beach team, which means I have to log attacks by each players for each operations. An operation is launched every day and all players should perform their attack in that operation.

I'd like to make it easier to list players who did not perform their attack, for each operation.

What I'd like is to have a coma-separated list of players whose attack is blank.

Here's a sample sheet to make it clearer.

What I'd like to automate is the red part, so I can simply copy-paste the value of that cell into our slack chatroom and kick some butts.

Of course, I could CONCAT a bunch of IFS, but the formula would be hell to maintain since I'm constantly adding new players and removing players.

In the end, if I could have a formula that says "For range B2:B6, when empty grab what's in column A and list them in a single cell, comma separated"

  • The spreadsheet you linked to does not have public access. Change its sharing so viewers just need a link. Also, without seeing the sheet, have you looked up the IF operation that can operate on a array?
    – user29020
    Commented Apr 29, 2015 at 15:09
  • Oops, sharing updated, sorry :( Commented Apr 29, 2015 at 15:18

1 Answer 1


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  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.