SumProduct 多列使用名称作为列索引 Vlookup 跨多张表

Question

我正在尝试使用 vlookup 对单独工作表上的表中的多个列求和，并将列索引号与名称（而不是列号）匹配。我可以返回单个结果，但似乎只能使用数组 {3,4,5} 等对列的乘积求和。

在一个选项卡中我有我的数据（本例已简化）

在另一张表中，我想显示 Fred 和 Bob 以及 Betty 和 Jane 的每个类别的总数。

这将返回单个结果：

=IFERROR(VLOOKUP(($A2&""),'P&L by job'!$A$2:$E$4,MATCH($A$2,'P&L by job'!$A$1:$E$1,0),FALSE),0)

此公式将得出总积：

=IFERROR(SUMPRODUCT(VLOOKUP(($A2&""),'P&L by job'!$A$2:$E$4{2,3},FALSE)),0)

问题是，当添加了额外的列或每次导入数据时列的顺序都不同时，我需要更改电子表格中的列索引号。

有没有更好的方法？

Answer 1

尝试剥离鲍勃、弗雷德、贝蒂和简来自列标题。

=SUM(INDEX('P&L by job'!$A:$Z, MATCH($A2, 'P&L by job'!$A:$A, 0), MATCH(REPLACE(B$1, FIND(" and ", B$1), LEN(B$1), TEXT(,)), 'P&L by job'!$1:$1, 0)),
     INDEX('P&L by job'!$A:$Z, MATCH($A2, 'P&L by job'!$A:$A, 0), MATCH(REPLACE(B$1, 1, FIND(" and ", B$1)+4, TEXT(,)), 'P&L by job'!$1:$1, 0)))

Answer 2

查找公式SUMPRODUCT并nested IF解决问题：

警告：

下面显示的方法最适合于 8 到 10 列这样的小数据范围，否则建议的方法/公式@Jeeped更好且明智之举。

怎么运行的：

• 在第 1 行（）中插入一些辅助数据S1:X1，如果需要，您可以扩展它们。
• 在第 9 行（）中写下姓名组合S9:U9，记住您可以根据需要更改顺序。

• 公式S10：

   =IFERROR(IF(S$1="A",SUMPRODUCT(VLOOKUP($R10,$R$3:$X$5,{6,7},FALSE)),IF(S$1="B",SUMPRODUCT(VLOOKUP($R10,$R$3:$X$5,{4,5},FALSE)),IF(S$1="C",SUMPRODUCT(VLOOKUP($R10,$R$3:$X$5,{2,3},FALSE)),""))),"")
  

注意：

• 在整个范围内填充公式。
• 您可以根据需要调整单元格引用和列的顺序。