excel - 多个索引的总和与一个值匹配

Question

需要有关 Excel 动态搜索基于两个不同表匹配的两列之和的帮助。

我已经输入了这张数据表一次

    A          B        C  
1   Qlty       Warp     Weft
2   Stpl.1     150      20
3   Cotn.1     80       60
4   Stpl.2     20       20
5   Cotn.2     20       20
6   Stpl.3     20       40

在A2:A6列中，Quality不能重复，它是唯一的名称

数据输入和报告表在这里

       A            B              C            D               E               F
8      Yarn Name    Sent Bags      Remaining    Qualty          Used Warp       Used Weft
9      20           800            600          Stpl.1          71              200
10     150          101            30           Stpl.2          70              30
11     40           300            290          Stpl.3          100             10
12     20                          400

C9:C5000 为返回列，值是根据列 A9:A5000( yarn 名称)计算的需要在 B2:B6 和/或 C2:C6 中查找 yarn 名称(例如:)“20”，无论它匹配在哪里，都从 A2:A6 索引该质量然后将返回的质量(可能不止一个)与 D9:D5000 相匹配，并对 E9:F5000 的数学结果求和

到目前为止我已经在 C12 中尝试过了

=SUMIF($A$9:$A12,A12,$B$9:$B12)-(SUMIF($D$9:$D12,INDEX($A$2:$A$6,MATCH(A12,$B$2:$B$6,0)),$D$9:$D12)+SUMIF($D$9:$D12,INDEX($A$2:$A$6,MATCH(A12,$C$2:$C$6,0)),$D$9:$D12))

附言:- 我使用的是 Excel 2007

Answer 1

如果我没理解错的话，下面的数组公式可以帮到你:

=SUM(IFERROR(INDEX($A$2:$A$6,N(IF(1,(MMULT(--($B$2:$C$6=A9),TRANSPOSE(COLUMN($B$2:$C$6)^0))>0)*(ROW($B$2:$C$6))-1)))=TRANSPOSE($D$9:$D$12),0)*TRANSPOSE($E$9:$E$12+$F$9:$F$12))

编辑后的数组公式按ctrl + shift + enter确认

编辑:

要分别计算经纱和纬纱列，请使用以下数组公式:

=SUM(IFERROR(INDEX($A$2:$A$6,N(IF(1,((A9=$B$2:$B$6)*ROW($B$2:$B$6))-1)))=TRANSPOSE($D$9:$D$12),0)*TRANSPOSE($E$9:$E$12))+SUM(IFERROR(INDEX($A$2:$A$6,N(IF(1,((A9=$C$2:$C$6)*ROW($C$2:$C$6))-1)))=TRANSPOSE($D$9:$D$12),0)*TRANSPOSE($F$9:$F$12))