Spreadsheet formula to count consecutive values in a column

Question

I have a column with "Y" and "N" values, and need a formula to count the last consecutive occurrences of "Y". So if I the column looks like this:

Y
Y
Y
N
Y
N
Y
Y

the desired result would be 2

Y
Y
Y
Y
N
N
Y

and here it would be 1

Y
Y
Y
Y
N

and here it would be 0. Is this possible with things like Excel, Numbers or Google Docs?

Answer 1

This little google apps script will make it happen:

function LASTCON(array) {
  // set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get data  
  var data = sh.getRange(array).getValues();

  // determine length of array
  var dLen = data.length; 

  // create yes counter
  var yesCount = 0;  

  // itterate through array (backwards)  
  for(var i=dLen-1; i>=0; i--) {
    if(data[i] == "Y") {      
      yesCount++;
    } else {
      break;
    }
  }

  // pass result to formula 
  return yesCount;
}

Add this script, via the script editor, to your spreadsheet and the function LASTCON is available throughout the worksheet, like so: =LASTCON("A1:A6")

See example file I've prepared: Last consecutive values in a column (editable)