EXCEL:应用于 INDEX MATCH 搜索的 SUMIFS 标准等于一个值

Question

我花了几乎一整天的时间试图弄清楚这一点。我在这里和其他各种网站上阅读了很多主题。这就是我想要做的:

我得到了总的销售产出。它很大，上面的项目数量取决于它查看的时间范围。系统中存在重大缺陷，我无法按地区获得数据。该信息不存储在系统中。记录仅存储客户姓名、产品信息、单位数量、价格和购买日期。我想获得按地区销售的每件商品的总数，以便我可以比较不同地区的商品受欢迎程度。

只有大约 50 个客户，所以我可以创建一个单独的表来为客户分配一个区域。

所以，我有三张纸:
表 1:销售

+-------------------------------------------------- ----+
|客户名称|产品 |金额 |价格 |日期 |
-------------------------------------------------- -----
|乔的鱼 | RT-01 | 7 | 5.45 | 2020/5/20 |
-------------------------------------------------- -----
|乔的鱼 | CB-23 | 17 | 0.55 | 2020/5/20 |
-------------------------------------------------- -----
|麦克的 bug | RT-01 | 4 | 4.45 | 2020/4/20 |
-------------------------------------------------- -----
|乔的鱼 | VX-28 | 1 | 1.20 | 2020/5/13 |
-------------------------------------------------- -----
|凯伦的\/| RT-01 | 9 | 3.45 | 2020/3/20 |
+-------------------------------------------------- ----+

表 2:地区

+------------------------+
|客户 |地区 |
----------------------
|乔的鱼 |不适用 |
----------------------
|麦克的 bug |不适用 |
----------------------
|凯伦的\/|欧盟 |
+------------------------+

我的结果在表 3 中:

+------------------------+
| |不适用 |欧盟 |
----------------------
| RT-01 | 11 | 9 |
+------------------------+

所以看看我为这个问题补的数据，我想比较一下在北美销售的 RW-01 和在欧洲销售的数量。如果我在销售表的末尾添加一个 INDEX MATCH 列，我可以做到这一点，但每次更新销售信息时我都必须这样做。
有没有办法做一个像这样的 SUMIFS:
SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,INDEX(Sheet2!$B:$B,MATCH(Sheet1!#Current A#,Sheet2!$A:$A))=Sheet3!$B2 ,Sheet1!$B:$B,Sheet3!$A3)
?

Answer 1

我认为使用 SUMIFS 很难做到这一点，因为您匹配的列必须是范围，但您当然可以使用 SUMPRODUCT 和 COUNTIFS 做到这一点:

=SUMPRODUCT(Sheet1!$C$2:$C$10*(Sheet1!$B$2:$B$10=$A2)*COUNTIFS(Sheet2!$A$2:$A$5,Sheet1!$A$2:$A$10,Sheet2!$B$2:$B$5,B$1))

我不建议使用全列引用，因为它可能会很慢。

顺便说一句，我假设 Sheet2 中没有针对客户和区域的特定组合的重复项 - 如果有，您可以使用

=SUMPRODUCT(Sheet1!$C$2:$C$10*(Sheet1!$B$2:$B$10=$A2)*
(COUNTIFS(Sheet2!$A$2:$A$5,Sheet1!$A$2:$A$10,Sheet2!$B$2:$B$5,B$1)>0))

编辑

值得使用公式的动态版本，尽管它并不优雅:

=SUM(Sheet1!$C2:INDEX(Sheet1!$C:$C,MATCH(2,1/(Sheet1!$C:$C<>"")))*(Sheet1!$B2:INDEX(Sheet1!$B:$B,MATCH(2,1/(Sheet1!$B:$B<>"")))=$A2)*
(COUNTIFS(Sheet2!$A$2:INDEX(Sheet2!$A:$A,MATCH(2,1/(Sheet2!$A:$A<>""))),Sheet1!$A2:INDEX(Sheet1!$A:$A,MATCH(2,1/(Sheet1!$A:$A<>""))),Sheet2!$B$2:INDEX(Sheet2!$B:$B,MATCH(2,1/(Sheet2!$B:$B<>""))),B$1)>0))