如何在多条件COUNTIFS语句中连接多个范围查找？

Question

我是在COUNTIFS语句的基础上构建的，我已经计算了某些东西。我想在COUNTIFS语句中添加另一个参数。

当前工作COUNTIFS：

=COUNTIFS('Fleet Maintenance Penetration'!$O$16:$O$1072,">="&DATEVALUE("5/1/2017"),'Fleet Maintenance Penetration'!$O$16:$O$1072,"<="&DATEVALUE("5/31/2017"),'Fleet Maintenance Penetration'!$N$16:$N$1072,"C",'Fleet Maintenance Penetration'!$C$16:$C$1072,"Abu Dhabi/Amiri Flight")

现在，我想获取该COUNTIFS并向其添加另一个参数。我要在工作簿的某个范围内查找特定值。与公式中的所有其它参考一样，该范围也参考工作表“舰队维护穿透”。

假设"P“的取值范围是R16:BH1072。我意识到我不能像这样写出代码：

=COUNTIFS('Fleet Maintenance Penetration'!$O$16:$O$1072,">="&DATEVALUE("5/1/2017"),'Fleet Maintenance Penetration'!$O$16:$O$1072,"<="&DATEVALUE("5/31/2017"),'Fleet Maintenance Penetration'!$N$16:$N$1072,"C",'Fleet Maintenance Penetration'!$C$16:$C$1072,"Air India",'Fleet Maintenance Penetration'!$R$16:$BH$1072,"P")

因此，我能够通过仅添加1列"R“的范围来成功地添加一个参数。

=COUNTIFS('Fleet Maintenance Penetration'!$O$16:$O$1072,">="&DATEVALUE("5/1/2017"),'Fleet Maintenance Penetration'!$O$16:$O$1072,"<="&DATEVALUE("5/31/2017"),'Fleet Maintenance Penetration'!$N$16:$N$1072,"C",'Fleet Maintenance Penetration'!$C$16:$C$1072,"Air India",'Fleet Maintenance Penetration'!$R$16:$R$1072,"P")

如何在此公式中添加或连接附加列S-BH？

Answer 1

COUNTIFS很棒，但不幸的是，它的规则和语法非常有限。一旦标准变得更加复杂，它就不能工作。另一方面，对于更复杂的条件，带数组的SUMPRODUCT是一种更灵活的解决方案。

你可以试试这个公式：

=SUMPRODUCT(
('Fleet Maintenance Penetration'!$O$16:$O$1072 >=Date(2017,5,1))*
('Fleet Maintenance Penetration'!$O$16:$O$1072<=Date(2017,5,31))*
('Fleet Maintenance Penetration'!$N$16:$N$1072="C")*
('Fleet Maintenance Penetration'!$C$16:$C$1072="Air India")*
('Fleet Maintenance Penetration'!$R$16:$BH$1072="P"))

附注：附加条件将尝试匹配相同行列R:BH中的任何"P"。如果同一行上存在多个"P"，则该行将被计算多次。