Possible to use ArrayFormula like Conditional Formatting Custom Formula?

Question

I have a simple Google Sheets spreadsheet that tracks which task a group of people have accomplished. As each person accomplishes a task, I use a simple checkbox to mark completion:

Task:       A   B   C   D
Human 1     [x] []  []  []
Human 2     [x] [x] [x] []
Human 3     []  [x] []  [x]
Human 4     [x] [x] [x] [x]

I'm looking for some way to have a final column that would summarize which tasks have NOT yet been completed, e.g:

Task:       A   B   C   D       Not Yet Completed:
Human 1     [x] []  []  []      B, C, D
Human 2     [x] [x] [x] []      D
Human 3     []  [x] []  [x]     A, C 
Human 4     [x] [x] [x] [x]     (Blank)

My goal is to do this using a single formula that will run through each row and concatenate the column names for the boxes that are unchecked.

I've tried using ARRAYFORMULA in conjunction with TEXTJOIN like so:

=TEXTJOIN(", ", TRUE, ARRAYFORMULA(IF(B2:E2=FALSE,$B$1:$E$1,"")))

This does what I want, but requires that I copy that formula to EVERY row I want to track.

When I try to apply the text join to a 2-dimensional range, it will join ALL of the text into a single cell, rather than applying it to each row one by one:

=TEXTJOIN(", ", TRUE, ARRAYFORMULA(IF(B2:E5=FALSE,$B$1:$E$1,"")))

---Result---
Task:       A   B   C   D       Not Yet Completed:
Human 1     [x] []  []  []      B, C, D, D, A, C
Human 2     [x] [x] [x] [] 
Human 3     []  [x] []  [x] 
Human 4     [x] [x] [x] [x] 

By contrast, when I set up a custom formula in Conditional Formatting, it allows me to "Apply to Range" (i.e. over multiple rows), but then enter a formula that is limited to a single row. It then applies this formula over each row in the range one by one.

is there any way to do this same "Apply to Range" concept within a cell using ARRAYFORMULA or other functions that are native to Google Sheets?

Answer 1

The Hard Way: Using & (this will work if you have a fixed number of columns otherwise you will have to keep updating the formula)

={"Not Yet Completed:";arrayformula(if(A2:A="","",substitute(trim(if(B2:B," ",B1&" ")&if(C2:C," ",C1&" ")&if(D2:D," ",D1&" ")&if(E2:E," ",E1&" "))," ",", ")))}

The Easy Way: Using Query Header (pretty much the same as player0 answer but a slightly alternative way of doing it)

={"Not Yet Completed:";arrayformula(if(A2:A="","",substitute(trim(transpose(query(transpose(if(B2:E5,"",B1:E1)),"",columns(B1:E1))))," ",", ")))}