将Arrayformula与SUMIF用于多种条件与通配符结合使用，以选择给定条件的所有值

发布于 2025-01-22 11:49:29 字数 1125 浏览 2 评论 0 原文

我使用 arrayformula 在多个条件下使用 sumif 使用＆amp; 串联条件，并且它起作用。现在，我想对特殊条件类似地使用此想法，以表明使用通配符（“所有”）为给定的列考虑所有值，但它行不通。

这是我的示例：

在 i2 上具有以下公式：

=ARRAYFORMULA(if(not(ISBLANK(H2:H)),sumif(B2:B & C2:C & D2:D & year(E2:E),
  if($G$2="ALL",B2:B,$G$2) & if($G$4="ALL",C2:C,$G$4) & if($G$6="ALL",D2:D,$G$6) &
  H2:H,A2:A),))

并且在我输入特定值时起作用，但是当我使用我的通配符时： ash all 指示对于给定的列/标准，应考虑所有值，都无法按预期工作。该方案应考虑所有标准都可以将其标记为所有在这种情况下，它将提供每年的数字总和。

这是我在Google电子表格中的测试示例：

note ：

我能够使用 sumproduct ' t在我的真实示例中使用 arrayformula 进行扩展
，我有更多条件

原文

I am using ARRAYFORMULA with multiple conditions using SUMIF concatenating the conditions using & and it works. Now I would like to use similarly this idea for a special condition indicating to consider all values using a wildcard ("ALL") for a given column, but it doesn't work.

Here is my example:

On I2if have the following formula:

=ARRAYFORMULA(if(not(ISBLANK(H2:H)),sumif(B2:B & C2:C & D2:D & year(E2:E),
  if($G$2="ALL",B2:B,$G$2) & if($G$4="ALL",C2:C,$G$4) & if($G$6="ALL",D2:D,$G$6) &
  H2:H,A2:A),))

and it works, when I enter specific values, but when I use my wildcard: ALL indicating that for a given column/criteria all values should be taken into consideration, it doesn't work as expected. The scenario should consider that all criteria can be labeled as ALLin such case it will provide the sum of NUM per year.

Here is my testing sample in Google Spreadsheet:

https://docs.google.com/spreadsheets/d/1c28KRQWgPCEdzVvwvXFOQ3Y13MBDjpEgKdfoLipFAOk/edit?usp=sharing

Notes:

I was able to get a solution for that using SUMPRODUCT but this function doesn't get expanded with ARRAYFORMULA
In my real example I have more conditions, so I am looking for a solution that escalates having more conditions

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

爱本泡沫多脆弱 2025-01-29 11:49:29

使用：

=QUERY(QUERY(FILTER(A2:E, 
 IF(G2="All", B2:B<>"×", B2:B=G2), 
 IF(G4="All", C2:C<>"×", C2:C=G4), 
 IF(G6="All", D2:D<>"×", D2:D=G6)), 
 "select year(Col5),sum(Col1) 
  where Col1 is not null
  group by year(Col5)"), 
  "offset 1", 0)

use:

=QUERY(QUERY(FILTER(A2:E, 
 IF(G2="All", B2:B<>"×", B2:B=G2), 
 IF(G4="All", C2:C<>"×", C2:C=G4), 
 IF(G6="All", D2:D<>"×", D2:D=G6)), 
 "select year(Col5),sum(Col1) 
  where Col1 is not null
  group by year(Col5)"), 
  "offset 1", 0)