CountIf 过滤数据 [英] CountIf With Filtered Data

问题描述

我正在研究一种方法来计算一列数据中零的数量，即使数据被过滤.我找到了以下解决方案:

I was researching a way to count the number of zeroes in a column of data, even if the data gets filtered. I found the following solution:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH("Pear",B2:B18))+0)

其中，B2:B18 是数据的总列表，"Pear" 是被统计的条件.

Where, B2:B18 is the total list of data and "Pear" is the criteria being counted.

• 有人能解释一下这个公式是如何完成这项任务的吗?
• 有没有更简单的方法来做到这一点?

推荐答案

我能够确定:

SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1))

用于返回一个数组，其中单元格在范围内可见和隐藏.可见返回1，隐藏返回0.

is used to return an array of which cells are visible and hidden in the range. 1 is returned for visible and 0 is returned for hidden.

ISNUMBER(SEARCH("Pear",B2:B18))+0)

用于返回一个数组，其中单元格包含 "Pear".如果找到梨"，则返回 1，否则返回 0.

is used to return an array of which cells contain "Pear". If "Pear" is found, 1 is returned, else 0.

SUMPRODUCT(arrayofvisiblecells , arrayofcellswithPear)

用于对单元格可见且存在梨"的所有时间求和.1*1 否则你将乘以 0.

is used to sum all of the times when the cell is visible AND "Pear" is present. 1*1 else you will be multiplying by a 0.

这篇关于CountIf 过滤数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！