我正在努力将一个条件集成到COUNTIFS语句中。我有大约5种条件可以很容易地解决,但我不知道最后一种。标准范围为A1:A40000,标准将计算与表2单元格A1:A40上30个文本字符串列表中任何值匹配的数字。这可能吗?我可以在没有其他条件的情况下得到结果。不幸的是,我没有灵活性在A1:A40000旁边添加一列,以检查它是否在列表中。
编辑:根据请求进行澄清。
我正在做的事情的简化版本。根据整个数据集中的列,我需要计算满足几个条件的项目数(列A)。因此,我需要找到B列中值为"1"的项数-AND-"C"列中的值为"YES"-AND-列"D"中的值"OLD"-AND(我正在处理的部分)-列"E"必须包含完全独立范围内的任何一个值(称之为Z1:Z40)。前3个条件的公式为:
=COUNTIFS(B:B,1, C:C,"YES", D:D,"OLD")
粗体的最终标准是:
=COUNTIFS(B:B,1, C:C,"YES", D:D,"OLD", **E:E,isnumber(match(E:E,Z1:Z40,0))**)
但这不起作用。。。
您可以简单地使用范围作为标准。如果你这样做,那么你的COUNTIFS
函数将返回一个数组(Z1:Z40
中的每个值各一个值),所以你需要一个函数来求和该数组——我使用SUMPRODUCT
,因为它不需要数组条目
=SUMPRODUCT(COUNTIFS(B:B,1,C:C,"yes",D:D,"old",E:E,Z1:Z40))
这种方法有一些局限性——在一个COUNTIFS
函数中只能使用两个"多项目"条件(如果您这样做,其中一个必须是列,另一个必须为行,或者您需要使用TRANSPOSE
才能做到这一点),并且Z1:Z40中的项目不应重复(否则您可能会重复计数)。
为了克服这两种限制,您可以使用SUMPRODUCT
代替COUNTIFS
,并将ISNUMBER(MATCH
用于多项目标准。如果你像那样使用SUMPRODUCT
,那么出于效率的原因,最好限制范围,例如
=SUMPRODUCT((B2:B100=1)*(C2:C100="yes")*(D2:D100="old")*ISNUMBER(MATCH(E2:E100,Z1:Z40,0)))
您可以根据需要添加任意多个ISNUMBER(MATCH
标准,Z1:Z40
可以是任何单行/列范围的
假设所有的头都在第1行,实际数据从第2行开始。我会在末尾添加一列,并输入公式
=IF(AND(B2=1, C2="YES", D2="OLD", COUNTIF($Z$1:$Z$40,E2)),"YES","NO")
然后向下复制,F列为"是"的任何一行都符合所有条件。
还有一种使用通配符的方法
=countifs(A1:D1;"*yes")
它统计所有包含"是"的单元格