Excel如何动态引用列首到倒数第二个单元格用于SUMPRODUCT公式

方案1：兼容所有Excel版本的通用写法 #

你可以用COUNTA配合INDEX构造动态范围，修改后的公式如下：

=SUMPRODUCT(--(A1:INDEX(A:A,COUNTA(A:A)-1)="o"),--(A2:INDEX(A:A,COUNTA(A:A))="f"))

说明：

• COUNTA(A:A)会自动统计A列所有非空单元格的总数，只要A列没有多余的无关非空内容，就能自动识别有效数据的最大行号
• INDEX(A:A,COUNTA(A:A)-1)会定位到A列倒数第二个有内容的单元格，整个引用范围会随着A列内容的增减自动调整长度，无需手动修改固定行号
• 第二个判断范围同步调整为A2:INDEX(A:A,COUNTA(A:A))，刚好和前一个范围错位对应，匹配你要统计相邻单元格符合「前为o、后为f」的需求

方案2：Excel 365/2021及以上版本简化写法 #

如果你使用支持动态数组的高版本Excel，可以用更简洁的写法：

=SUM(--(DROP(TOCOL(A:A,1),-1)="o")*--(DROP(TOCOL(A:A,1),1)="f"))

说明：

• TOCOL(A:A,1)会提取A列所有非空单元格，自动忽略空值
• DROP(TOCOL(A:A,1),-1)去掉序列最后一个元素，得到从第一个到倒数第二个的所有值
• DROP(TOCOL(A:A,1),1)去掉序列第一个元素，得到从第二个到最后一个的所有值，两者天然错位对齐，计算逻辑和原公式一致

注意：如果A列存在表头、备注等无关非空内容，需要对应调整COUNTA或TOCOL的引用范围，排除无关内容即可。

内容的提问来源于stack exchange，提问作者Mary A.