google-sheets - 有或有条件的 SUMIFS

Question

我正在尝试使用包含或依赖于单元格引用的条件的 SUMIFS 公式。

我已经解决了我能想到的所有部分解决方案，但仍然无法得到适用于最终结果的东西。

---

预期的解决方案是：

=sumifs(A:A,B:B,{"x","y"})

如果 B 中的值为“x”或“y”，则将 A:A 求和（这是一个 MCVE，实际情况需要多个条件，包括日期比较，见下文）

---

对于文字值，这是一个解决方案：

=ArrayFormula(sumifs(A:A,regexmatch(B:B,"x|y"),TRUE))

使用 SUMIF（但不是 SUMIFS），这有效：

=sum(arrayformula(sumif(B:B,{E2,"y"},A:A)))

多个 SUMIFS 工作，但加数的数量随着附加条件呈指数增长：

=sumifs(A:A,B:B,E2)+sumifs(A:A,B:B,E3)

以下两个公式适用于 Excel，但不适用于 Google 表格：

=SUM(SUMIFS(A:A,B:B,{"x","y"}))

=SUM(SUMIFS(A:A,B:B,E2:E3))

MCVE 表：https ://docs.google.com/spreadsheets/d/11q9RXEr84WuyuAXXmGwkGSdwaKznXD0IyGj187XYp1I/edit

---

这是当前工作表上单元格 C78 中实际公式的当前状态

=(sumifs('Sheet2'!$C:$C,'Sheet2'!$B:$B,$A78,'Sheet2'!$D:$D,">="&C$1,'Sheet2'!$D:$D,"<"&D$1)
+ArrayFormula(sumifs('Sheet2'!$I:$I,regexmatch('Sheet2'!$J:$J,"CAD|^$"),TRUE,'Sheet2'!$H:$H,$A78,'Sheet2'!$K:$K,"<"&D$1,'Sheet2'!$L:$L, ">"&C$1))
+ArrayFormula(sumifs('Sheet2'!$I:$I,regexmatch('Sheet2'!$J:$J,"CAD|^$"),TRUE,'Sheet2'!$H:$H,$A78,'Sheet2'!$K:$K,"<"&D$1,'Sheet2'!$L:$L, "="))
+sumifs('Sheet2'!$I:$I,'Sheet2'!$J:$J,"USD",'Sheet2'!$H:$H,$A78,'Sheet2'!$K:$K,"<"&D$1,'Sheet2'!$L:$L, ">"&C$1)*C$110
+sumifs('Sheet2'!$I:$I,'Sheet2'!$J:$J,"USD",'Sheet2'!$H:$H,$A78,'Sheet2'!$K:$K,"<"&D$1,'Sheet2'!$L:$L, "=")*C$110)
*if(C$1 > TODAY(), 0, 1)

当前工作表在第 1 行有每月日期，在 A 列有类别，在第 110 行有每月汇率。

说明：添加以下5项

1. 当月一次性支出
2. 本月存在但尚未结束的以加元或空白货币计的经常性费用
3. 本月存在且永久的以加元或空白货币计的经常性费用
4. 本月存在但尚未结束的以美元计的经常性费用（乘以汇率）
5. 本月存在且永久的以美元计的经常性费用（乘以汇率）

我正在尝试至少结合项目 2+3 和 4+5（如果不是完全 2-5）

Sheet2 有 2 个部分：

1. 一次性项目，类别在 B 列，值在 C 列，日期在 D 列
2. 经常性项目，类别在 H 列，值在 I 列，货币在 J 列（空白时表示 CAD），开始日期在 K 列，结束日期在 L 列（空白时表示永久）

标签： google-sheetsgoogle-sheets-formula

Answer 1

使用SUMPRODUCT：

=SUMPRODUCT((B2:B4={"x","y"})*A2:A4)

或ARRAYFORMULA/ SUM：

=ArrayFormula(SUM((B2:B4={"x","y"})*A2:A4))

使用单元格参考TRANSPOSE：

=SUMPRODUCT((B2:B4=TRANSPOSE(E2:E3))*A2:A4)

=ArrayFormula(SUM((B2:B4=TRANSPOSE(E2:E3))*A2:A4))