0

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: enter image description here

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.

1
0

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 
  ) 
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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