组合多个SumIFS

Question

我正在尝试使用一个特定的市场X合并几个帐户。

如何简化下面的公式以将数组用于第二个条件？

=SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A20)
+SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A21)
+SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A22)
+SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A23)
+SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A24)
*SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A25)
+SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A26)
+SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A27)
+SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A28)
*SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A29)
*SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A30)
+SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A31)
+SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A32)
+SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A33)
+SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A34)
*SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A35)

Answer 1

使用SUMPRODUCT()包装器：

=SUMPRODUCT(SUMIFS('Report Data'!$H$4:$H$6169,'Report Data'!$E$4:$E$6169,'SG&A by Spend'!$A$3,'Report Data'!$G$4:$G$6169,'SG&A by Spend'!$A$20:$A$35))