How to count the number of cells which contain a specific text in the edited row

Question

[Goal] When a user edits a cell, I want the script to count the number of cells that contains the text "No" from a certain range (Colum B to K). From there, based on the number cells that contain the text "No," I want to insert certain text in cells of Column A (Header Name: AD/NAD).

Here's a sample sheet that you can refer to to understand the look of it. https://docs.google.com/spreadsheets/d/17A10THQL7X_DvoxORvBfPmfs1C_wizKj41oV5CVT0j4/edit?usp=sharing

[Difficulty] I've built the script so that when the user edits the cells between Column B and K, it would count the number of cells that contain the text "No." However, it's not counting correctly at all and I don't understand why.

[Code Sample]

function onEdit(e) {
  var dsatAnalysisSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DSAT Analysis");

  const range = e.range;
  const row = range.getRow();
  const column = range.getColumn();
  const adNADColumn = "A";
  const c1ColumnNum = 2;
  const c2ColumnNum = 3;
  const c3ColumnNum = 4;
  const c4ColumnNum = 5;
  const c5ColumnNum = 6;
  const c6ColumnNum = 7;
  const c7ColumnNum = 8;
  const c8ColumnNum = 9;
  const c9ColumnNum = 10;
  const c10ColumnNum = 11;

  if (column == c1ColumnNum) {
    checkPointForADNAD(dsatAnalysisSheet, adNADColumn, row);

  } else if (column == c2ColumnNum) {
    checkPointForADNAD(dsatAnalysisSheet, adNADColumn, row);

  } else if (column == c3ColumnNum) {
    checkPointForADNAD(dsatAnalysisSheet, adNADColumn, row);

  } else if (column == c4ColumnNum) {
    checkPointForADNAD(dsatAnalysisSheet, adNADColumn, row);

  } else if (column == c5ColumnNum) {
    checkPointForADNAD(dsatAnalysisSheet, adNADColumn, row);

  } else if (column == c6ColumnNum) {
    checkPointForADNAD(dsatAnalysisSheet, adNADColumn, row);

  } else if (column == c7ColumnNum) {
    checkPointForADNAD(dsatAnalysisSheet, adNADColumn, row);

  } else if (column == c8ColumnNum) {
    checkPointForADNAD(dsatAnalysisSheet, adNADColumn, row);

  } else if (column == c9ColumnNum) {
    checkPointForADNAD(dsatAnalysisSheet, adNADColumn, row);

  } else if (column == c10ColumnNum) {
    checkPointForADNAD(dsatAnalysisSheet, adNADColumn, row);

  }

}

function checkPointForADNAD(dsatAnalysisSheet, adNADColumn, row) {
  var checkPointColumnNum = 11;
  var firstValueColumn = "B";
  var lastValueColumn = "K";
  var noCountValue = 0;
  var checkValueOptions = dsatAnalysisSheet.getRange(firstValueColumn + row + ":" + lastValueColumn + row).getValues();
  Logger.log(checkValueOptions);
  Logger.log(noCountValue);

  for (i = 0; i < checkPointColumnNum; i++) {
    Logger.log(noCountValue);
    if (checkValueOptions[0][i] == "No") {
      noCountValue = i + 1;
      Logger.log(noCountValue);
    }
  }

  if (noCountValue == 0) {
    dsatAnalysisSheet.getRange(adNADColumn + row).setValue("NAD");

  } else if (noCountValue == 1) {
    dsatAnalysisSheet.getRange(adNADColumn + row).setValue("Mostly NAD");

  } else if (noCountValue == 2) {
    dsatAnalysisSheet.getRange(adNADColumn + row).setValue("Barely NAD");

  } else if (noCountValue == 3) {
    dsatAnalysisSheet.getRange(adNADColumn + row).setValue("Mostly AD");

  } else if (noCountValue >= 4) {
    dsatAnalysisSheet.getRange(adNADColumn + row).setValue("AD");

  }

}

user_3889423 · Answer

If a formula based approach is ok, you can try in cell A1

={"AD/NAD"; INDEX(IF(LEN(B2:B), VLOOKUP(MMULT(--(B2:K="No"), SEQUENCE(COUNTA(B1:K1), 1, 1, 0)), {0, "NAD"; 1, "Mostly NAD"; 2, "Barely NAD"; 3, "Mostly AD"; 4, "AD"}, 2, 1),))}

or

={"AD/NAD"; INDEX(IF(LEN(B2:B), VLOOKUP(COUNTIF(IF(B2:K="No", ROW(B2:B)), ROW(B2:B)), {0, "NAD"; 1, "Mostly NAD"; 2, "Barely NAD"; 3, "Mostly AD"; 4, "AD"}, 2, 1),))}

and see if that works?

user_14265469 · Answer

Something like this:

function onEdit(e) {
  if (e.range.rowStart < 2) return;
  if (e.range.columnStart < 2 || e.range.columnStart > 11) return;

  var sheet = e.source.getActiveSheet();
  if (sheet.getName() != 'Sheet1') return;

  var row = sheet.getRange(e.range.rowStart,2,1,11).getValues().flat();
  var counter = row.filter(x => x == 'No').length;

  var counter_text = '';

  if (counter <= 1) counter_text = 'Mostly NAD';
  if (counter == 2) counter_text = 'Barely NAD';
  if (counter == 3) counter_text = 'Mostly AD';
  if (counter >= 4) counter_text = 'AD';

  sheet.getRange(e.range.rowStart,1).setValue(counter_text);
}

How to count the number of cells which contain a specific text in the edited row

2 Answers