count if the condition in a row is valid

Count if the condition in a row is valid

Following is the contents of a spreadsheet:

In a cell i want a count that checks each row the value "Website" at Column A, and the value "x" at column C. If both are true in the same row, then the counter have to count.

In another cell i want a count that checks each row the value "Onlineshop" at Column A, and the value "x" at column D. If both are true in the same row, then the counter have to count.

Well, here is the solution for above condition checking:

function validateCondition() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var rows = sheet.getDataRange();

  var numRows = rows.getNumRows();

  var values = rows.getValues();

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  SpreadsheetApp.setActiveSheet(ss.getSheetByName("Sheet1"));//whatever your sheet name is, here mine is Sheet1

  var range = SpreadsheetApp.getActiveSheet().getDataRange();

  

  var counter1 = 0;

  var counter2 = 0;

  

  for (var i = 0; i <= numRows - 1; i++) {

    

    var valueAtColumn1 = range.offset(i, 0, 1, 1).getValue(); //Here 0 is for column1 "A"

    //var valueAtColumn2 = range.offset(i, 1, 1, 1).getValue(); //Here 1 is for column2 "B"

    var valueAtColumn3 = range.offset(i, 2, 1, 1).getValue(); //Here 2 is for column3 "C"

    var valueAtColumn4 = range.offset(i, 3, 1, 1).getValue(); //Here 3 is for column4 "D"

  

    if (valueAtColumn1 == "Website" && valueAtColumn3 == 'x' ) {

      counter1++;

    } 

    

    if (valueAtColumn1 == "Onlineshop" && valueAtColumn4 == 'x' ) {

      counter2++;

    }

  }

  

  var cell_1 = sheet.getRange('F3'); // this will fill cell 'F3' with total number of counter 1

  cell_1.setValue(counter1) ;

  var cell_2 = sheet.getRange('F4'); // this will fill cell 'F4' with total number of counter 2

  cell_2.setValue(counter2) ;

};

Just add the above function in to the script with help of script editor and it will do the job of conditional counters.

This script will give you the results of counter1 in cell "F3", and results of counter 2 in cell "F4".

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive