Want Query to return first column and count across the row

Question

I have multiple google sheets with student names and scores from different courses. I am trying to query those sheets to return the student's name and a count of completed assignments in a totals sheet. The data sheets have student names in column A and columns G to BK are individual assignments.

For example:

I would like the result columns to show a count of 1 for John, 2 for Suzie, and 3 for Alice.

So far I've tried various iterations of "Select A, Count(G:BK,'<>')" I can make a separate column for the counts using the formula above and it works but I don't want to re-create that every week when I refresh the sheets.

Any help with counting across rows in a query would be useful. Thank you.

Answer 1

One way to do that is to use a query smush and string manipulation, like this:

=arrayformula( 
  query( 
    { 
      Sheet1!F2:F, 
      len( 
        regexreplace( 
          regexreplace( 
            transpose( query( transpose(Sheet1!G2:BK), "", 9^9 ) ), 
            "\d+", "µ" 
          ), 
          " ", "" 
        ) 
      ) 
    }, 
    "where Col1 is not null", 0 
  ) 
)