Googlesheets count single cell across multiple sheets

user_8330413 0
Viewed 61

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.

Example: Count across sheets

countif google-query-language google-apps-script google-sheets arrays
1 Answers

either you hardcode it like:

=COUNTIF({Anthony!$A1,Lee!$A1,Cornelius!$A1,Liam!$A1}, B1)

enter image description here

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&""")"})
user_56326290
Related