Counting attendance across multiple sheets

Question

This past semester I distance-taught a lecture course with an enrollment of 229. There were 14 lectures all together. Students wrote comments for each lecture they viewed on a Google Form specific to that lecture, and the results were to sent to 14 sheets in a Google Spreadsheet. This worked very nicely, but when I try to tally the number of times each student attended, using a SUMPRODUCT, COUNTIF, INDIRECT formula that works quite nicely in Excel, Google Spreadsheet only seems to check one of the sheets. Here is a copy of the spreadsheet, with student names, email addresses, and comments deleted for obvious privacy reasons.
https://docs.google.com/spreadsheets/d/1XqbgSPPfMNvRgAieOswAzeigjkZRuLT_orOh24gR1x4/edit?usp=sharing
The thing I want to count is occurrences of unique student IDs, which are a 7-character string. Here's what it looks like in Google Spreadsheets versus what it looks like in Excel. In the sheet titled "Tally," the A column is the names of the other 14 sheets. The D column is the student IDs of the 229 enrollees. The E column is where the formula is, and is supposed to return the number times an ID appears in the D column of the 14 sheets. It would be nice if the final Spreadsheet is something I could export and open in Excel or Apple Numbers without the formulae breaking.
Thanks in advance for any help you can offer.
Rachel

Gill K · Accepted Answer

Hi Rachel,
 
I have an answer that I think works, but I'm sure that there are better ones.  Hopefully, one of the other experts will provide one.
 
See here, in tab GK.Help, added to your sheet.
 
I'm not familiar with it in Excel, but Sheets doesn't (to my knowledge) allow for a dynamic array inside an INDIRECT function.
So this:
=INDIRECT("'"&A$1:A$14&"'!D$2:D$230")
 
does not return a big array from all the sheets, it just uses the first value from A1 as the INDIRECT parameter, as you found out.
So I've entered all of the INDIRECTS, and stacked them vertically to give the same result.  With this formula:
 
=SUM(COUNTIF({ 
INDIRECT("'"&$A$1&"'!D2:D230"); INDIRECT("'"&$A$2&"'!D2:D230");
INDIRECT("'"&$A$3&"'!D2:D230"); INDIRECT("'"&$A$4&"'!D2:D230"); 
INDIRECT("'"&$A$5&"'!D2:D230"); INDIRECT("'"&$A$6&"'!D2:D230");
INDIRECT("'"&$A$7&"'!D2:D230"); INDIRECT("'"&$A$8&"'!D2:D230"); INDIRECT("'"&$A$9&"'!D2:D230"); INDIRECT("'"&$A$10&"'!D2:D230"); INDIRECT("'"&$A$11&"'!D2:D230");INDIRECT("'"&$A$12&"'!D2:D230");
INDIRECT("'"&$A$13&"'!D2:D230");INDIRECT("'"&$A$14&"'!D2:D230")},
"="&D1))
 
This formula then needs to be dragged down the column.
 
Let me know if that works for you for now, until someone provides something better.
Cheers,
Gill

Gill K · Accepted Answer

Absolutely.
 
You could create a tab that is just two columns, the dates, and the student IDs responding on those dates.
 
Then it would be very easy, in Sheets, Excel, or Numbers, to do a simple count for each student ID.
 
This formula, the main part of the one I provided earlier, creates the data for a new tab.
Create a blank new tab in your sheet - the second you gave me, that we just fixed today - , and put this formula in cell A1.
 
={"Date","Student ID"; SORT({ 
INDIRECT("'"&Tally!$A$1& "'!A2:A230"),INDIRECT("'"&Tally!$A$1& "'!D2:D230"); INDIRECT("'"&Tally!$A$2& "'!A2:A230"),INDIRECT("'"&Tally!$A$2& "'!D2:D230"); INDIRECT("'"&Tally!$A$3& "'!A2:A230"),INDIRECT("'"&Tally!$A$3& "'!D2:D230"); INDIRECT("'"&Tally!$A$4& "'!A2:A230"),INDIRECT("'"&Tally!$A$4& "'!D2:D230"); INDIRECT("'"&Tally!$A$5& "'!A2:A230"),INDIRECT("'"&Tally!$A$5& "'!D2:D230"); INDIRECT("'"&Tally!$A$6& "'!A2:A230"),INDIRECT("'"&Tally!$A$6& "'!D2:D230"); INDIRECT("'"&Tally!$A$7& "'!A2:A230"),INDIRECT("'"&Tally!$A$7& "'!D2:D230"); INDIRECT("'"&Tally!$A$8& "'!A2:A230"),INDIRECT("'"&Tally!$A$8& "'!D2:D230"); INDIRECT("'"&Tally!$A$9& "'!A2:A230"),INDIRECT("'"&Tally!$A$9& "'!D2:D230"); INDIRECT("'"&Tally!$A$10&"'!A2:A230"),INDIRECT("'"&Tally!$A$10&"'!D2:D230"); INDIRECT("'"&Tally!$A$11&"'!A2:A230"),INDIRECT("'"&Tally!$A$11&"'!D2:D230"); INDIRECT("'"&Tally!$A$12&"'!A2:A230"),INDIRECT("'"&Tally!$A$12&"'!D2:D230"); INDIRECT("'"&Tally!$A$13&"'!A2:A230"),INDIRECT("'"&Tally!$A$13&"'!D2:D230"); INDIRECT("'"&Tally!$A$14&"'!A2:A230"),INDIRECT("'"&Tally!$A$14&"'!D2:D230")}) }
 
You could put this anywhere on the Tally tab if you prefer, rather than on a new tab.
I've demoed it in tab GK.Testing.
 
Let me know if you run into any issues.
Gill

Gill K · Accepted Answer

Hi Rachel,
 
I fixed the formula in your second sheet.  It was doing the COUNTIF from all the sheets by checking the student ID to cell D1, instead of B1.  In this sheet, column A is your list of tab names, and column B is your student IDs, whereas in the earlier sheet, the student IDs were in column D.
 
Also, I realised that there is no need for the SUM function, since the COUNTIF is adding up how many occurences of each student ID there are.  So the formula here is:
 
=COUNTIF({ 
INDIRECT("'"&$A$1&"'!D2:D230"); INDIRECT("'"&$A$2&"'!D2:D230"); INDIRECT("'"&$A$3&"'!D2:D230"); INDIRECT("'"&$A$4&"'!D2:D230"); INDIRECT("'"&$A$5&"'!D2:D230"); INDIRECT("'"&$A$6&"'!D2:D230"); INDIRECT("'"&$A$7&"'!D2:D230"); INDIRECT("'"&$A$8&"'!D2:D230"); INDIRECT("'"&$A$9&"'!D2:D230"); INDIRECT("'"&$A$10&"'!D2:D230"); INDIRECT("'"&$A$11&"'!D2:D230");INDIRECT("'"&$A$12&"'!D2:D230"); INDIRECT("'"&$A$13&"'!D2:D230");INDIRECT("'"&$A$14&"'!D2:D230")},
  "="&B1)
 
Here, the 14 INDIRECT functions inside the braces are each reading column D2:D230 from their respective sheets, stacking all of the student IDs from the 14 sheets into one tall (virtual) column.
 
The COUNTIF then goes down this column, counting up the ones that equal the value in cell  B1, the first student ID.
 
This formula has to be dragged down, and the B1 gets revised on each row to compare to B2, then B3, etc.  Only the B1 value changes, as the formula is dragged down, since the other cell references are absolute, by using a dollar sign, $.  Or they are part of the INDIRECT string, which, as a text string, doesn't get modified by dragging down.  I hope that makes sense.
 
It is possible to produce almost the same result with a single QUERY, which doesn't need to be dragged down, but it would only report the student IDs that actual exist in the 14 tabs, and some of your IDs, which show a count of zero here, don't occur anywhere in the tabs.  Like your first two, 215N048 and 216N709.
 
Lastly, I'm not sure whether or not this would work in Excel, or Numbers, but even though these spreadsheet applications have most of the same functions common to them, there are some differences that may prevent a specific formula from working in the other environments.
 
If you need a formula that can work, unchanged, in all three environments, that would need to be a separate question, I think.
 
Let me know if this helps.
Gill