Google Sheets – Counting Matches Between Answer Key and Student Response

Hm, that's an interesting question. If you're looking for just the n of terms in the array, you can do this without splitting the terms.

=SUM(ARRAYFORMULA(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4, ",", "")) + 1))

Alright, here's what's happening in this formula.

• In A1, we take the len() of the string and subtract the len() of the string after substituting the comma. In this case we get 9 - 9 = 0. We then add 1 to get 1 (the number of terms in the cell)
• We can use arrayformula() on this to apply the same subtraction function to every cell in the given range (in this case A1:A4)
• The final piece is to sum() the numbers from the resulting range and get the total # of terms that are split on the comma.

Let's look at cell A2 to see a case of when the comma is used. We take len(A2) which is 10 and subtract len(A2) after substituting the comma for a null character "" which is 9. We get 10 - 9 = 1 then we add 1 to the total to get 2 which is equal to the number of terms in the cell.

Hope this helps!

I had a similar problem. sheetSpider does not appear to be available anymore. I tried writing my own script and it kinda worked but it was slow and didn't update reliably when the source data changed.

I eventually solved my problem with a pivot table (Data->Pivot table report...). Just select the range, then add all the columns you want to see as Rows (sounds counterintuitive I know) and set Filters for Column B > 1 and Column D = teacher's name. Create a new pivot table for each teacher on its own sheet where the second filter varies. More details about pivot tables in Google Sheets here. For me it is quick and updates well whenever the source data changes.