我想要一个列公式COUNTIF
,Brand 3
并且如果经过过滤则COUNTIF
根据过滤条件进行更改。
TOTAL COUNT 0 0 3 COUNTIF(H3:H5,"VoID")
Area Region Branch Member Brand 1 Brand 2 Brand 3
East NY NY 1 Void
West CA LA 2 Void
North IL Chicago 3 Void
应用过滤器后,我得到相同的总数,但需要它是 1(仅计算可见单元格):
TOTAL COUNT 0 0 3 Answer needs to be 1
Area Region Branch Member Brand 1 Brand 2 Brand 3
East NY NY 1 Void
答案1
你可以使用这个公式
=SUMPRODUCT(SUBTOTAL(3,OFFSET(H3,ROW(H3:H5)-ROW(H3),0)),(H3:H5="void")+0)
H3:H5
这将为您提供(过滤后)可见“空白”单元格的数量。