Excel 365中筛选数据后结合SUBTOTAL与COUNTIF统计特定条件的预订数量

针对你在Excel 365里遇到的「按入住年份筛选后，统计预订提前天数小于D4数值的订单数量」的问题，我来给你几个可行的解决方案，同时帮你理清之前尝试的问题所在：

一、推荐解决方案（SUMPRODUCT组合SUBTOTAL） #

这个方法能精准识别筛选后的可见行，同时统计符合条件的数量，公式直接放在C4单元格即可：

=SUMPRODUCT(--(SUBTOTAL(103,OFFSET(D7:D240,ROW(D7:D240)-ROW(D7),0,1))=1),--(D7:D240<D4))

我来拆解一下这个公式的逻辑：

• OFFSET(D7:D240,ROW(D7:D240)-ROW(D7),0,1)：把D7到D240的区域拆分成单个单元格的独立区域，方便逐个判断行是否可见。
• SUBTOTAL(103, ...)：对每个单个单元格执行「可见性判断」——如果当前行是筛选后的可见行，返回1；隐藏行则返回0。
• --(...)：把布尔判断结果（TRUE/FALSE）转换成数值1或0，方便后续计算。
• 两个数组相乘后，SUMPRODUCT会自动求和，最终得到**同时满足「行可见」和「提前天数<D4」**的订单总数。

二、更简洁的动态数组方案（Excel 365专属） #

因为你用的是Excel 365，支持动态数组函数，可以用更直观的写法：

=COUNT(FILTER(D7:D240,SUBTOTAL(103,OFFSET(D7:D240,ROW(D7:D240)-ROW(D7),0,1))=1,"")<D4)

或者用BYROW+LAMBDA的组合：

=SUM(BYROW(D7:D240,LAMBDA(x,IF(SUBTOTAL(103,x)=1,IF(x<D4,1,0),0))))

这两个公式的核心逻辑和第一个方案一致，只是用了365的新函数简化了写法，结果完全相同。

三、为什么你之前的尝试没成功？ #

• =COUNTIF(D7:D240,"<10")：COUNTIF函数不会识别筛选后的隐藏行，它会计算整个区域里符合条件的所有数据，包括被筛选掉的行，所以结果不符合预期。
• =SUBTOTAL(3,D7:D240)<10：这个公式是先统计可见行的总数，然后判断这个总数是否小于10，完全不是你需要的「统计符合条件的可见行数量」，逻辑方向错了~

小提示 #

• 确保D列的「预订提前天数」是数值格式，如果是文本格式，条件判断会失效；
• 当D4的数值变化时，公式会自动更新结果，不需要手动修改公式里的数值。

备注：内容来源于stack exchange，提问作者Tmc_m