Google Sheets – Count Nonempty Words and Find Identicals

google sheets

I'm working on a project that involves translating a few thousand different words from English to German. I've gotten back five different translations, but not all the translations are complete.

I have a Google Sheet with English in column A and the different translations in B, C, D, E and F. Any given row will always have an English word in A, but some cells may be empty as the different translators didn't always recommend a word when they didn't know what to use (some of the terms are highly technical).

I'd like to use a formula to tell me how many translations in each row are present (1, 2, 3, 4 or 5 out of 5).

In the next column, I also want to use a formula to determine how many translations are perfectly identical (1, 2, 3, 4 or 5 out of 5).

Can this be done? If the identical translations formula wouldn't work, can anyone think of a formula I can use to tell me the most popular of the translations?

Best Answer

G2:

=5-COUNTBLANK(B2:F2)

H2:

=ARRAYFORMULA(SORTN(TRANSPOSE({B2:F2;COUNTIF(B2:F2,B2:F2)}),1,2,2,0))

COUNTIF to count number of occurrences of each distinct entity
SORTN to get the most popular one in the descending order of counts.

Code

function getTotals() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var uniqueNames = [], allData = [], output = [];

  // itterate through the sheets and retrieve info  
  for(var i=1; i<14; i++) {
    this.sh = ss.getSheetByName(i); 
    this.lRow = this.sh.getMaxRows();
    this.names = this.sh.getDataRange().getValues();
    var a = this.names;

    // gather unique names and data
    for(var j=4, jLen=a.length; j<jLen; j++) {
      if(a[j][1].length > 0) {
        if(uniqueNames.indexOf(a[j][1]) == -1) {
          uniqueNames.push(a[j][1]);
        }          
        allData.push([a[j][1],a[j][2],a[j][3]]);
      }
    }    
  }

  // sort all the data for efficient processing
  var sData = allData.sort(), sNames = uniqueNames.sort();

  // itterate throught the names and perform sum
  for(var l=0, lLen=sNames.length; l<lLen; l++) {
    var sum1=0, sum2=0;
    for(var m=0, mLen=sData.length; m<mLen; m++) {
      if(sNames[l] == sData[m][0]) {
        sum1 += sData[m][1], sum2 += sData[m][2];
      }      
    }
    output.push([sNames[l], sum1, sum2]);
  }
  return output;
}

Remark

Make a copy of the spreadsheet and goto Tools>Script editor

Example

I've created a copy of your file and added the solution to it: Sum Through Sheets

Google Sheets – Count Values Meeting a Condition in Another Table

To put it simply, you want to find the size of the intersection of two arrays. Taking the intersection is discussed here. Adapting it to your case results in the following:

=counta(filter('Form responses 1'!C1:O1, 'Form responses 1'!C2:O2="Agree", match('Form responses 1'!C1:O1, C2:C21, 0)))

The filter command applies the required criteria, then counta counts the values that passed the filter.

Best Answer

Related Solutions

Google Sheets – Get Total Count from Matching Sheets

Code

Remark

Example

Google Sheets – Count Values Meeting a Condition in Another Table

Related Topic