筛选后再按平常的方式用函数按条件计数、按条件求和等会出现错误。这个时候怎么办？推荐用SUBTOTAL+OFFSET组合。

在求和和计数前先看一个筛选后的序号处理。

1.筛选后序号也保持连续

默认情况下，筛选后序号是不连续的，如下。

如何让筛选后序号保持连续？

取消筛选，然后在A2中输入下方公式生成序号：

=SUBTOTAL(3,$B$1:B2)-1

再筛选，序号保持了连续。

2.筛选后按条件计数

接着上方，求筛选后产品销售大于150的有多少人。

直接输入公式=COUNTIF(D2:D17,">150")的话，结果是错的。

当前的错误在于它统计的是整个数据而不是筛选后的数据。

同序号类似，要用SUBTOTAL对可见单元格计数。

公式=SUMPRODUCT(SUBTOTAL(3,OFFSET($D$1,ROW(1:16),))*(D2:D17>150))

公式解析：

最核心的是SUBTOTAL(3,OFFSET($D$1,ROW(1:16),))。OFFSET($D$1,ROW(1:16),)逐一取D1下方的第1、2、3……16行数据。实际就是将D2:D17单元格分别引用一次。然后用SUBTOTAL判断每个数是否可见，可见就计数为1，不可见计数为0，得到一组由1和0组成的数组。最后与大于150的条件判断结果相乘，并通过SUMPRODUCT对乘积求和。

3.筛选后求和和按条件求和

1）求和

很简单，与序号处理类似，只是把3改成了9。如图。

2）按条件求和

求单价大于10的销售数量。处理办法与筛选后按条件计数类似。

公式=SUMPRODUCT(SUBTOTAL(3,OFFSET($D$1,ROW(1:16),))*D2:D17*(E2:E17>10))

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育Excel精品好课任你选择！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

双色图表直接表明业绩是否超过平均值

一文讲懂indirect函数在跨表汇总数据时的使用方法

Excel动态图表入门

用SORTBY函数进行排序

版权申明：

本文作者Mutou；部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。