多个excel OR条件

Question

请求帮助：

=IF(AND(OR(
S:S="Sales Hub",
S:S="Office Integration",
S:S="Opportunities",
S:S="Export to Excel",
S:S="Gamification",
S:S="LinkedIn",
S:S="Leads")),
"Project One",
IF(AND(OR(
S:S="Invoices",
S:S="Products and Pricelists",
S:S="Quotes and Orders")),
"Project Two",
IF(AND(OR(
S:S="Quick Campaigns",
S:S="Marketing Lists")),
"Project Three",
""
)
)
)

即使我有匹配的值来满足嵌套OR条件中指定的Project Two和Three，上面的公式也只返回Project One for all。

Answer 1

看起来您想检查S列上是否存在任何指定值，如果为true，则返回Project#。

您可以将COUNTIF与数组条件和SUM组合使用。如果大于0，则表示已满足条件。

=IF(SUM(COUNTIF(S:S;{"Sales Hub";"Office Integration";"Opportunities";"Export to Excel";"Gamification";"LinkedIn";"Leads"}))>0;"Project One";IF(SUM(COUNTIF(S:S;{"Invoices";"Products and Pricelists";"Quotes and Orders"}))>0;"Project Two";IF(SUM(COUNTIF(S:S;{"Quick Campaigns";"Marketing Lists"}))>0;"Project Three";"Error")))