Google Sheets – How to Count Occurrences of a Number in a Column

With the following Google Apps Script, you can count the way you want to, in a Google Spreadsheet.

Code

/**
 * myWords(searchRange, checkRange)
 * @param {Array.Array} searchRange
 * @param {Array.Array} checkRange
 * @customfunction {int} 
 */
function myWords(searchRange, checkRange) {
  // small error handling
  if(searchRange[0].length > 1 || checkRange[0].length > 1) {
    throw "one of the ranges has more than one column";
    return;
  }  

  // counting of words
  var output = [];  
  for(var k=0, kLen=searchRange.length; k<kLen; k++) {
    var count = 0; 
    var cell = searchRange[k][0];   
    for(var i=0, iLen=checkRange.length; i<iLen; i++) {
      var spl = checkRange[i][0].split(" ");
      if(spl.length == 1) {
        if(spl == cell) {
          count++;
        }
      } else {
        for(var j=0, jLen=spl.length; j<jLen; j++) {
          if(spl[j] == cell) {
             count++;
          }
        }
      }
    }
    output.push([count]);
  }

  // return individual outcomes
  return output;
}

Screenshot

Note

This piece of code will also work in the new Google Spreadsheet. Paste the code in the script editor, under Tools>Script editor. Press the save button and you can start using the script, as shown in the screenshot.

Given that:

first sheet column A is the id, column D is the field we want to mark X on.

Second sheet column B is were the id will be.

You can Use a function like this in column D:

D2 = VLOOKUP(Sheet1!A2, Sheet2!B1:B700, Sheet2!AA2, FALSE)

This returns the value from column AA in sheet 2. To get the exact result you want, you need to be not using that column, and to have put X's into. But you can combine with VLOOKUP formula with others eg. ISNA() to get various results.