如何在使用COUNTIFS、COUNTIF和SUMIF函数时仅统计可见行

你遇到的问题很常见——默认的COUNTIF/SUMIF/COUNTIFS函数不会自动忽略筛选隐藏的行，要实现仅统计可见行，我们需要结合SUMPRODUCT和SUBTOTAL函数来完成，下面是针对你每个现有公式的修改版本：

1. 原公式：=COUNTIF(USSW1!V7:V10000,110)+COUNTIF(USSW2!V7:V10000,110)-AB6 #

修改后（仅统计可见行）：

=SUMPRODUCT(SUBTOTAL(103,OFFSET(USSW1!V7:V10000,ROW(USSW1!V7:V10000)-ROW(USSW1!V7),0,1))*(USSW1!V7:V10000=110)) + SUMPRODUCT(SUBTOTAL(103,OFFSET(USSW2!V7:V10000,ROW(USSW2!V7:V10000)-ROW(USSW2!V7),0,1))*(USSW2!V7:V10000=110)) - AB6

2. 原公式：=SUMIF(USSW1!$V7:$V10000,"110",USSW1!O7:O10000)-SUMIFS(USSW1!O7:O10000,USSW1!M7:M10000,"FP",USSW1!V7:V10000,"110") #

修改后（仅统计可见行）：

=SUMPRODUCT(SUBTOTAL(103,OFFSET(USSW1!V7:V10000,ROW(USSW1!V7:V10000)-ROW(USSW1!V7),0,1))*(USSW1!V7:V10000="110")*USSW1!O7:O10000) - SUMPRODUCT(SUBTOTAL(103,OFFSET(USSW1!M7:M10000,ROW(USSW1!M7:M10000)-ROW(USSW1!M7),0,1))*(USSW1!M7:M10000="FP")*(USSW1!V7:V10000="110")*USSW1!O7:O10000)

3. 原公式：=COUNTIFS(USSW1!V7:V10000,"109",USSW1!M7:M10000,"FP")+COUNTIFS(USSW2!V7:V10000,"109",USSW2!M7:M10000,"FP") #

修改后（仅统计可见行）：

=SUMPRODUCT(SUBTOTAL(103,OFFSET(USSW1!V7:V10000,ROW(USSW1!V7:V10000)-ROW(USSW1!V7),0,1))*(USSW1!V7:V10000="109")*(USSW1!M7:M10000="FP")) + SUMPRODUCT(SUBTOTAL(103,OFFSET(USSW2!V7:V10000,ROW(USSW2!V7:V10000)-ROW(USSW2!V7),0,1))*(USSW2!V7:V10000="109")*(USSW2!M7:M10000="FP"))

4. 原公式：=SUMIF(USSW1!$V7:$V10000,"110",USSW1!O7:O10000)-SUMIFS(USSW1!O7:O10000,USSW1!M7:M10000,"CA",USSW1!V7:V10000,"110")-SUMIFS(USSW1!O7:O10000,USSW1!M7:M10000,"CF",USSW1!V7:V10000,"110") #

修改后（仅统计可见行）：

=SUMPRODUCT(SUBTOTAL(103,OFFSET(USSW1!V7:V10000,ROW(USSW1!V7:V10000)-ROW(USSW1!V7),0,1))*(USSW1!V7:V10000="110")*USSW1!O7:O10000) - SUMPRODUCT(SUBTOTAL(103,OFFSET(USSW1!M7:M10000,ROW(USSW1!M7:M10000)-ROW(USSW1!M7),0,1))*(USSW1!M7:M10000="CA")*(USSW1!V7:V10000="110")*USSW1!O7:O10000) - SUMPRODUCT(SUBTOTAL(103,OFFSET(USSW1!M7:M10000,ROW(USSW1!M7:M10000)-ROW(USSW1!M7),0,1))*(USSW1!M7:M10000="CF")*(USSW1!V7:V10000="110")*USSW1!O7:O10000)

5. 原公式：=SUMIF(USSW1!$V7:$V10000,"110",USSW1!O7:O10000) #

修改后（仅统计可见行）：

=SUMPRODUCT(SUBTOTAL(103,OFFSET(USSW1!V7:V10000,ROW(USSW1!V7:V10000)-ROW(USSW1!V7),0,1))*(USSW1!V7:V10000="110")*USSW1!O7:O10000)

原理说明 #

• SUBTOTAL(103, ...)：这里的103对应COUNT函数，会自动忽略自动筛选隐藏的行和手动隐藏的行；如果你的场景需要包含手动隐藏的行，只需要把103改成3即可。
• OFFSET(区域, 偏移量, 0, 1)：生成单个单元格的引用，让SUBTOTAL可以逐行判断该行是否可见（可见返回1，隐藏返回0）。
• SUMPRODUCT：将SUBTOTAL返回的可见性标记、条件判断结果（符合条件返回1，否则0）以及要求和的数值相乘，最后求和，就得到了仅可见行中符合条件的计数或求和结果。

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