Excel SUMPRODUCT 与 OR 和 { } 中的多个条件

Question

我想要使​​用 sumproduct 来求和，其中：

name = {"john","an","hui","james"}
OR
Number= {"one","two","five","six","ten"}
SUM
table1[Salary]


=SUMPRODUCT(((table1[Name] = {"john","an","hui","james"})+(table1[Number]= {"one","two","five","six","ten"}))*(table1[Salary]))

但是我收到了错误消息#N/A，您能帮助我完成上述功能吗？

Answer 1

尝试

=SUMPRODUCT(GESTEP(ISNUMBER(MATCH(Table1[Name],{"john","an","hui","james"},0))+ISNUMBER(MATCH(Table1[Number],{"one","two","five","six","ten"},0)),1),Table1[Salary])