将Arrayformula与SUMIF用于多种条件，并使用通配符结合条件。几个月的结果

Question

这是一个类似的问题，与使用sumif的arrayformula在多种条件下与通配符结合使用通配符，以选择给定条件的所有值，但要在给定的一年的所有月份中获得第一列的总和。我试图推断 @player0提供的相同解决方案，但是 by 不起作用，因为我想获得一年中的每个月的结果，无论源。这是示例：

在列中 g 中我们有不同的过滤条件，它可以包括一个特殊令牌： all 包括每个值的所有值标准。在这一年中，我们可以选择要将结果总结为一年中的每个月的给定年份。

i 使用引用问题的类似想法具有预期的结果，但是它不能在 arrayformula （查询结果不展开）中表达。

=IFNA(QUERY(QUERY(FILTER($A$2:$E, 
 IF($G$2="All", $B$2:$B<>"×", $B$2:$B=$G$2), 
 IF($G$4="All", $C$2:$C<>"×", $C$2:$C=$G$4), 
 IF($G$6="All", $D$2:$D<>"×", $D$2:$D=$G$6),
 YEAR($E$2:$E) = $G$8
 ), 
 "select sum(Col1) where month(Col5) =" & MONTH($H2) - 1), 
  "offset 1", 0),"")

列 > j 数组尝试不起作用，因为我们不能使用 sumif 使用虚拟范围，可以通过使用 filter 创建辅助列来解决它，但是我我正在寻找不需要的解决方案。

=Arrayformula(if(not(ISBLANK(H2:H)), sumif(
Filter(FILTER($A$2:$E, IF($G$3="All", $B$2:$B<>"×",
 $B$2:$B=$G$3), IF($G$5="All", $C$2:$C<>"×", $C$2:$C=$G$5), 
 IF($G$7="All", $D$2:$D<>"×", $D$2:$D=$G$7), 
 YEAR($E$2:$E) = $G$9),{1,0,0,0,0}), H2:H,
Filter(FILTER($A$2:$E, IF($G$3="All", $B$2:$B<>"×", 
 $B$2:$B=$G$3), IF($G$5="All", $C$2:$C<>"×", $C$2:$C=$G$5), 
 IF($G$7="All", $D$2:$D<>"×", $D$2:$D=$G$7), 
  YEAR($E$2:$E) = $G$9), {0,0,0,0,1})
),))

这是样本电子表格：

Answer 1

尝试：

=INDEX(IFNA(VLOOKUP(MONTH(H2:H13), QUERY(QUERY(FILTER(A2:E, 
 IF(G3="All", B2:B<>"×", B2:B=G3), 
 IF(G5="All", C2:C<>"×", C2:C=G5), 
 IF(G7="All", D2:D<>"×", D2:D=G7)), 
 "select month(Col5)+1,sum(Col1) 
  where Col1 is not null "&IF(G9="",," 
    and year(Col5)="&G9)&  
 "group by month(Col5)+1"), 
  "offset 1", 0), 2, 0)))

Answer 2

此公式在样本 Sheet1 的单元 L2 中。这是一个 sumif（），它使用正则和 mmult 来创建 3' s的数组，其中满足条件和＆amp;'符合“ SUMIF”标准的月份。

=ARRAYFORMULA(SUMIF(MMULT(N(REGEXMATCH(B2:D,SUBSTITUTE({G3,G5,G7},"ALL",))),
 {1;1;1})&EOMONTH(E2:E,-1)+1,3&H2:H13,A2:A))

详细说明
（公式，然后在以下几行中）

SUBSTITUTE({G3,G5,G7},"ALL",)

生成 1x3 带有 g3，g5，g7，g7 值的数组，如果“ all”将被空字符串替换。如果是（“ all”，all”，“ all”）返回：（,,，）。替代的结果将是在<代码> REGEXMATCH 在（“全部”，“ ALL”）的情况下。

REGEXMATCH(B2:D,SUBSTITUTE({G3,G5,G7},"ALL",))

返回与 b2相同大小的数组）值和函数 n（）将其转换为（ 0 | 1 ）值。

MMULT(N(REGEXMATCH(B2:D,SUBSTITUTE({G3,G5,G7},"ALL",))),
 {1;1;1})

乘以两个矩阵： mx3 x 3x1 并返回数组 mx1 。如果满足所有过滤器条件，将在给定单元格上返回数字 3 （我们有三个条件），否则比 3 低的数字。

EOMONTH(E2:E,-1)+1

计算细胞 e2的上个月的最后一天：我们需要将这些日期与代表本月第一天的 H2：H 中的日期进行比较。

SUMIF(range, criterion, [sum_range])

• range ：将包含一个 mx1 的数组，其中一个值： {0,1,1,2,3} ，取决于如何满足许多条件。它附加了＆amp; eomonth（e2：e，-1）+1 。请记住，日期存储为整数编号。

• 标准： 3＆amp; h2：h13 参考日期由数字前缀 3 。

  。

• sum_range ：范围 a2：a 我们要基于范围和标准匹配的总和，因此只有 3 值的行 mmult 将被考虑。

在这里，在过滤器的情况下，结果具有值：

此解决方案当所有 doken不使用：

note ：与预期结果和 @player0解决方案的唯一区别是，当没有匹配时，它返回 0 。