求助：基于多条件重启COUNTIF函数的实现方案

我有如下表格，目前用=COUNTIF(B$2:B2,B2)统计特定条目的出现次数，但需要实现当Flag列（D列）出现1时重启计数器。例如第8行Counter列（C列）公式应为=COUNTIF(B$8:B8,B8)，后续行（如第9行）继续从该起点计数，直到再次遇到Flag为1的行。理想状态是按日期查找当前条目前最近的Flag=1的行，而非仅按表格顺序。

我编写了以下脚本，它能将Flag=1的行Counter设为0，初始行的COUNTIF公式也正确，但Flag=1之后的行公式起始范围仍为B$2，不符合需求：

function setCountifFormula() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
  var data = sheet.getDataRange().getValues();
  
  for (var i = 1; i < data.length; i++) { //iterate through each row
    var colBValue = data[i][1]; //get columnB in i
    var colAValue = data[i][0]; // get date in i
    var colDValue = data[i][3]; // get flag in i
    var closestRow = 1; // empty variable
    
    if( colDValue == "1") { //if columnD = 1 
      sheet.getRange(i+1,3).setValue(0); // set columnC = 0
    } else {
      for (var j = 1; j < data.length; j++) { //iterate through other rows
        if (data[j][1] === colBValue && data[j][3] === "1") { // if columnB in j = ColumnB in i, and flag in row j = 1 
          var dateToCompare = data[j][0]; //set datetoCompare = date in row j
          closestRow = j;
          if (dateToCompare < colAValue) {
            var range = "B$" + (closestRow + 1) + ":B" + (i + 1);
            var formula = "=COUNTIF(" + range + ",B" + (i + 1) + ")";
            sheet.getRange(i + 1, 3).setFormula(formula);
          } else {
            var range = "B$2:B" + (i+1);
            var formula = "=COUNTIF(" + range + ",B" + (i+1) + ")";
            sheet.getRange(i+1, 3).setFormula(formula);
          }
        }
      }
      if (closestRow === 1) {
        var range = "B$2:B" +(i+1);
        var formula = "=COUNTIF("+range +",B"+(i+1)+")";
        sheet.getRange(i+1,3).setFormula(formula);
      }
    }
  }
}

方案一：无需脚本，用公式实现 #

在C2单元格输入以下公式，下拉填充即可：

=IF(D2=1,0,COUNTIF(INDIRECT("B"&(MAXIFS(A$1:A1,B$1:B1,B2,D$1:D1,1)+1)&":B"&ROW()),B2))

公式逻辑： #

• MAXIFS(A$1:A1,B$1:B1,B2,D$1:D1,1)：查找当前行上方，与当前行Item Name相同且Flag=1的最大行号（即最近的符合条件的行）
• INDIRECT("B"&(上述结果+1)&":B"&ROW())：构建从最近Flag=1行的下一行到当前行的统计范围
• 若当前行Flag=1，直接返回0；否则用COUNTIF统计范围内当前Item的出现次数

方案二：修复现有脚本 #

原脚本的核心问题是未正确筛选出日期小于当前行的最近Flag=1条目，且遍历方向错误导致覆盖结果。修改后的脚本如下：

function setCountifFormula() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
  var data = sheet.getDataRange().getValues();
  var lastRow = data.length;

  for (var i = 1; i < lastRow; i++) {
    var currentItem = data[i][1];
    var currentDate = data[i][0];
    var currentFlag = data[i][3];
    var startRow = 2; // 默认起始行是第2行

    if (currentFlag === "1") {
      sheet.getRange(i + 1, 3).setValue(0);
      continue;
    }

    // 从当前行向上查找最近的符合条件的行
    var latestFlagRow = -1;
    for (var j = i - 1; j >= 0; j--) {
      if (data[j][1] === currentItem && data[j][3] === "1" && data[j][0] < currentDate) {
        latestFlagRow = j;
        break; // 找到第一个就停止，确保是最近的行
      }
    }

    if (latestFlagRow !== -1) {
      startRow = latestFlagRow + 2; // 数组索引转实际行号
    }

    var range = `B$${startRow}:B${i + 1}`;
    var formula = `=COUNTIF(${range},B${i + 1})`;
    sheet.getRange(i + 1, 3).setFormula(formula);
  }
}

修改要点： #

• 改为从当前行向上遍历，找到第一个符合条件的行即停止，确保取最近的前置行
• 增加日期小于当前行的筛选条件
• 简化逻辑，避免重复设置公式

内容的提问来源于stack exchange，提问作者Maria Fernanda Contreras