☰

如何为 countif 公式在多列中添加范围？

tag-icon • tag-icon worksheet-function countif
如何为 countif 公式在多列中添加范围？

我有一张工作表，我想在多列中计算满足单个条件的列数。我遇到一个问题，如果我在“范围”下选择多列，它会在选择第一列后将其视为条件-

目前的公式是 -

=COUNTIF($J$8:$J$14,"US")

有效的公式是这样的，但非常繁琐，因为我有超过 50 个范围可供选择-

=COUNTIFS(D8:D9,"IT")+COUNTIF(J8:J9,"IT")+COUNTIF(V8:V9,"IT")

在此处输入图片描述

有没有更简单的公式可以让我仅选择具有相同条件的多个列中的范围？

在此处输入图片描述

答案1

IT从问题和评论来看，您似乎想要获得第 7 行右侧所有列中出现的次数总数。Allocated例如，您的数据以两列为一组。一个条目是Allocated和Region，下一个是Interim和Region，等等。如果这是正确的，那么类似这样的方法可以解决任何大小的区域的问题：

=LET(allData,C8:T9,prevHeader,OFFSET(allData,-1,-1,1),targetData,FILTER(allData,prevHeader="Allocated"),SUM(IF(targetData="IT",1,0)))

这依赖于LET()和FILTER()函数，因此，如果您的 Excel 版本没有这些函数，则此方法将不起作用。不过，此答案底部有一个公式的替代版本，应该可以使用。该LET()函数允许您将复杂的公式重命名为单个变量，以便以后更轻松地引用。我们可以将上面的大公式分解为这些更容易理解的部分：

  • allData,C8:T9将您的所有数据（不包括标题）保存为变量allData。
  • prevHeader,OFFSET(allData,-1,-1,1)通过向上移动一行、向左移动一列来偏移数据，并且只有一行高。
  • targetData,FILTER(allData,prevHeader="Allocated")allData返回左侧列标题为“已分配”的所有单元格。这将是“已分配”列之后的“区域”列的所有数据。
  • SUM(IF(targetData="IT",1,0))将所有“IT”值变为 1，其他所有值变为 0，然后将它们相加。COUNTIF()由于我们的使用方式，该方法不起作用溢出函数。这是最后一个参数，因此它不会与变量名匹配，而只是作为结果返回。

这一切的好处在于，可以很容易地构建一个小表，引用该小表的行和列标题，然后创建一个填充所有数据的公式：

表选项1

中的公式W8为：

=LET(allData,$C$8:$T$9,prevHeader,OFFSET(allData,-1,-1,1),targetData,FILTER(allData,prevHeader=W$7),SUM(IF(targetData=$V8,1,0)))

请注意，我们必须将其中一些范围引用设置为绝对 ( $) 而不是相对，但不是全部。这样我们就可以将该公式复制/粘贴到我们漂亮的小表格上，所有内容都会根据需要进行更新。

如果你没有 LET() 和 FILTER()

你可以放弃所有花哨的部分并写得更短，但在我看来，更难遵循公式：

=SUM(IF(IF(OFFSET(C8:T9,-1,-1,1)="Allocated",C8:T9,0)="IT",1,0))

该公式是通过采用此答案顶部的原始公式并将重命名的部分一点一点地替换回公式中而创建的。我们还必须将 替换FILTER()为IF(~,1,0)。您也可以像上面一样将其制作成漂亮的表格。公式将改为：

=SUM(IF(IF(OFFSET($C$8:$T$9,-1,-1,1)=W$7,$C$8:$T$9,0)=$V8,1,0))

相关内容