Looking for a formula that will count the value of a single cell across multiple (a whole class of students) sheets. I am aware of using to do this but for 30 students I'd want something more dynamic like using a range for example.
or use script:
function SNAME(option) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()
var thisSheet = sheet.getName();
if(option === 0){ // ACTIVE SHEET NAME =SNAME(0)
return thisSheet;
}else if(option === 1){ // ALL SHEET NAMES =SNAME(1)
var sheetList = [];
ss.getSheets().forEach(function(val){
sheetList.push(val.getName())
});
return sheetList;
}else if(option === 2){ // SPREADSHEET NAME =SNAME(2)
return ss.getName();
}else{
return "#N/A"; // ERROR MESSAGE
};
};
=INDEX({"","","";"=COUNTIF({"&TEXTJOIN(";", 1, "INDIRECT("""&QUERY(SNAME(1),
"where Col1 <> '"&SNAME(0)&"'")&"!A1"")")&"}, """&A1:C1&""")"})