使用vlookup作为Countifs内部的标准

Question

经过多年使用这个社区，我有一个无法找到答案的问题。希望大家能提供帮助！

我试图计算每个人逾期的“接地”项目数量。我的屏幕截图（ count tracker ）显示了我正在使用的内容的非常简洁的版本。列AC是我的大（15,000行）数据字段，它是从外部源导入的。在GH列中，我列出了每个可能的任务（约100行）和类型（接地和非接地以及许多其他任务）。 F列是我试图获得计数的地方。

我在F9中的公式当前使用countifs来获取等于E9（在这种情况下为“ smith”）的大数组中的实例，而大数组中的截止日期小于或等于F8中的当前日期。我不知道的是如何添加以计算大数组中的任务，其中H列中的任务类型等于“接地”。我尝试了Vlookup，但似乎不喜欢它，并告诉我该公式存在问题。它不起作用，但这是我所追求的一般想法：

=COUNTIFS($A$2:$A$7,$E9,$C$2:$C$7,"<="&$F$8,VLOOKUP($B$2:$B$7,$G$2:$H$4,2,FALSE),"=Grounded")

凭借目前的数据，史密斯的数量应该为1，而琼斯的数量应该为0。我有一种感觉比我要做的简单，但我无法弄清楚。我正在使用Excel 2016。

希望我对问题解释得很好。谢谢您的帮助。

Answer 1

使用Excel 2016，我会说mmult（）在这里是一个很好的选择：

< img src =“ https://i.sstatic.net/fccij.png” alt =“在此处输入图像说明”>

f9中的cse-formula：

=SUM((A$2:A$7=E9)*(MMULT((TRANSPOSE(G$2:G$4)=B$2:B$7)*(TRANSPOSE(H$2:H$4)="Grounding"),ROW(G$2:G$4)^0))*(C$2:C$7<=F$8))

Answer 2

您可以添加第四列存储该任务是否扎根于

D2的公式将是：

=INDEX($H$2:$H$4,MATCH(B2,$G$2:$G$4,0))

修改您的Countifs公式以将此新列用作标准

=COUNTIFS($A$2:$A$7,$E9,$C$2:$C$7,"<="&$F$8,$D$2:$D$7, "Grounded")

Answer 3

尝试此SumProduct+vlookup公式无辅助解决方案，并且可以

在f9中为您的Excel 2016工作，公式复制：

=SUMPRODUCT((A$2:A$7=E9)*(VLOOKUP(T(IF({1},$B$2:$B$7)),G$2:H$4,2,0)="Grounded")*($C$2:$C$7<=$F$8))