Google Sheets 统计列内累计SUMPRODUCT未超阈值的单元格计数方法

原公式的逻辑问题 #

• 公式嵌套层级错位，把逐行加权累计值计算、范围匹配、阈值判定三个流程混在一起，最终导致COUNTIF直接返回统计范围内的全量单元格总数，匹配不上逐行的判定条件
• 具体问题拆解：

1. 逐行SUMPRODUCT折算逻辑的区域范围没对齐，没法生成可逐行运行的数组计算结果
2. SUMIF参数位置不符合语法要求，把没算完的数组直接当判定范围传进去，没形成“先算逐行累计值、再做阈值对比”的执行顺序
3. 缺了逐行匹配的数组计算逻辑，最后计数结果完全不符合实际判定规则

修正后可用公式 #

=ArrayFormula(COUNTIF(MMULT(N(ROW(AB13:AB167)>=TRANSPOSE(ROW(AB13:AB167))),IF(X13:X167<>"",1/X13:X167,1)),"<="&ABS(AB9)))

公式运行逻辑 #

• 用MMULT矩阵运算逐行生成从AB13起始行到当前行的累计折算值，折算规则完全匹配表格批注的要求：X列对应单元格非空时取1/X列值作为权重，为空时取1作为权重
• 直接对生成的全量累计值数组执行COUNTIF判定，统计累计值小于等于ABS(AB9)阈值的单元格数量，不会出现原公式返回全量单元格数的问题
• 全流程在数组维度完成计算，没有嵌套层级错位的问题，运算逻辑和判定规则一一对应

内容的提问来源于stack exchange，提问作者Jeremy