Improving Count, CountIF Google Sheets formulas

Question

I have created a google sheets. There are multiple sheets (Tabs) inside single google sheet.

I have one sheet called Dashboard

There are other sheets like kkweb, virtual. Currently there are 2 other sheets except dashboard. All the 2 sheets has same columns & structure, Even new sheets will have same columns & structure.

I want to perform some calculations on dashboard sheet.

Count Total Tasks

=COUNT(kkweb!B5:B,virtual!B5:B)

Count Total "New Requests"

=(COUNTIF(kkweb!D5:D,"New Requests")) + (COUNTIF(virtual!D5:D,"New Requests"))

Note: My above formulas are working perfectly, But I'm going to have like 20+ sheets instead of 2. So my count formula will be like

=COUNT(kkweb!B5:B,virtual!B5:B,Sheet3!B5:B,Sheet4!B5:B,Sheet5!B5:B,Sheet6!B5:B,Sheet7!B5:B) etc

It will be very long for all of my formulas, every time a new sheet comes.

Is there any easy way to do calculations in google sheets?

We maybe can create extra sheet with name config and add sheet names there & perform an easy formula to do above calulations

Note: Each sheet will have a different name, not sheet1, sheet2, sheet3.

Secondly, How can i calculate value based on other column from all sheets.

For example: =(COUNTIF(kkweb!D5:D,"New Requests")) This formulae is counting where value is New Requests from D5:D in kkweb sheet. I have another column C5:C which contains names "John", "Katrina" etc

So basically I want to check how many count of New Requests are for "John"

player0 player0 · Accepted Answer · 2019-09-13T09:39:29

there are several ways how to solve this. it depends on how far you want to go, eg. which route you prefer. internally there is no formula to detect sheet names so for that you will need a script. there are several for example:

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
  };
};

then you can build upon it and create for example a string generator

=ARRAYFORMULA(TEXTJOIN(",", 1, SNAME(1)&"!D5:D"))

or even full formula ready to be copy-pasted:

=ARRAYFORMULA("=COUNT('"&TEXTJOIN(",'", 1, SNAME(1)&"'!B5:B")&")")

of course, it can be fully managed via script if you are skilled in JS and have it run with some fancy onOpen/onEdit or time trigger

as for the John/Katrina issue you can use COUNTIFS instead of COUNTIF. there you can define multiple criteria to get your desired count. for example:

=COUNTIFS(kkweb!D5:D, "New Requests", kkweb!E5:E, "John")

Improving Count, CountIF Google Sheets formulas

1 Answers