How can I get a list of people that don't have a number in their column?

Question

I have a Google Spreadsheet with a list of jobs of a group I'm part of. In it I have the information of when was the last time a person did a job and how many times he/she has made it. I want to be able to best distribute jobs to people. So I created a line showing who done a job less times and another that shows who did it more. I used VLookup for this.

Now I want to be able to get a list of the people who never did a specific job. How can I do this?

The spreadsheet is here: https://docs.google.com/spreadsheets/d/1OaVuriXfynruCdFfRag5IITBLnvejFX2h7pRSBtA6Xo/edit#gid=0

Answer 1

Try filter() using sumif() to calculate row-by-row totals, like this:

=filter( 
  B4:B32; 
  not( sumif( 
    if(column(C4:L32); row(C4:L32)); 
    row(C4:L32); 
    C4:L32 
  ) ) 
)

The rows where the sum of numbers is zero are the ones where the person never did any job. To limit the formula to a specific job, adjust the range C4:L32 so that it points to that job's columns.