使用表/命名范围代替单细胞标准的SumIF

Question

我在工作簿中有两个工作表(Sheet1，Sheet2)。

表2包含有5列的表(名为Table1)：

• 外卖
• 户户
• 服装
• 燃料
• 食品杂货

在第一页，我有两栏：

• 费用名称
• 费用共计

现在，我想做的是：

1. 设置费用名称的范围(范围1)
2. 设置支出总额的范围(范围2)
3. 将范围1与表中的相应列进行比较，并仅将匹配的值相加。

例如，在范围1 (B6:B16)：

• BP
• 加德士
• McDonalds
• 肯德基

范围2 (C6:C16)：

• 300
• 400
• 200
• 150

现在，我要做的就是把外卖(McDonalds，肯德基)的值加起来，并排除任何不符合标准的东西。

因此，我的总和将是所有的外卖事件--如果它们列在我的表格中--在本例中是350。

但我似乎不能让公式起作用。

我使用了这些资料来源：

https://exceljet.net/excel-functions/excel-sumifs-function

为SUMIF函数选择命名范围的特定列

最后得到了这个公式：

=SUMIF($B$6:$B$16;Table1[Takeaways];C6:C16)

这一消息来源：

https://excelchamps.com/blog/sumif-sumifs-or-logic/

最后得到了这个公式：

=SUM(SUMIFS(C6:C16;B6:B16;Table1[Takeaways]))

这两个公式都返回0。

但是，如果将Table1Takeaways改为"McDonalds"，那么它将正确识别范围1中"McDonalds“一词的每个出现。

编辑：

我已经更新了上面的公式，以匹配下面的图像。

这是包含引用的表：

​

​

此表包含以下数据：

​

​

公式：

单元格C4 (外卖旁边)：=SUMIF($B$6:B$16;Table1[Takeaways];C6:C16)

电池C5 (燃料旁边)：=SUM(SUMIFS(C6:C16;B6:B16;Table1[Fuel]))

似乎只有BP在公式中被检测到。

当我使用带有单个单元格引用的公式而不是表或已使用的范围时，这是一个输出表：

​

​

公式：

单元格F4 (BP旁边)：=SUMIF($B$6:B$16;"BP";C6:C16)

单元格F5 (位于Caltex旁边)：=SUM(SUMIFS(C6:C16;B6:B16;"Caltex"))

单元格F6 (McDonalds旁边)：=SUMIF($B$6:B$16;"McDonalds";C6:C16)

单元格F7 (肯德基旁边)：=SUM(SUMIFS(C6:C16;B6:B16;"KFC"))

Answer 1

如果没有看到L和K中的数据，我就不能给你一个完整的答案--但这很可能与你拉数组的方式有关

试试类似于此的东西

=SUMPRODUCT(SUMIFS($L$11:$L$43,$K$11:$K$43,CHOOSE({1,2},Takeaways,"anything else you wanted to sum")))

记住SUMIFS是针对多个标准的，所以如果您只计算一个，则需要=SUMPRODUCT(SUMIF(

以上工作方式仅适用于垂直向量，但更改命名范围，使2列表改为2个命名范围应该是可以的--除非它是您需求的一部分

表2将改为expense_Name和expense_Total等。

Answer 2

如果我正确理解你想要达到的目标，我认为你的设置在概念上是不正确的。

看起来你在试图跟踪支出，每一项支出(或受款人)都被分配到一个类别中(“外卖”、“家庭”等)。从关系模型的角度来看，您的第二个表(定义每个费用/受款人的类别)应该只有两个列(或变量)：费用名称和费用类别。

​

​

您设置的表('Sheet 2')使用类别(即可能的值)作为不同的列(即变量)。但是只有变量，即“费用类别”，类别本身就是可能的值。

如果您这样设置它，问题就会改变:您可以在第一个表中添加一个依赖列，该表显示每个受款人的类别(或“费用名称”)，使用来自第二个表的VLOOKUP()。

​

​

然后，您可以对所有与该类别匹配的受款人的费用进行汇总。

​

​

注意事项：我使用LibreOffice Calc创建了插图，因此可能有一些小的差异，但逻辑是相同的。

Answer 3

我正要结束这个问题，作为我自己的问题这里的一个重复，但在使用命名范围，我认为有一点不同。然而，这背后的逻辑大致相同。

在下面进一步讨论我的部分解决方案时，我得出了以下公式：

=SUMPRODUCT(COUNTIF(Table1[Takeaways];Range1)*Range2)

COUNTIF()部件计算表中单元格值出现的次数。因此，请确保表中没有重复项。如果该值存在于表中，则COUNTIF()的结果将为0。通过这种方法，我们创建了一个1和0的矩阵，通过乘法和使用SUMPRODUCT()，我们迫使excel进行矩阵计算并返回正确的结果。

偏解

我使用了以下公式：

=SUMPRODUCT(ISNUMBER(MATCH(Range1;Table1[Takeaways]))*Range2)

该公式做了以下工作：

• MATCH()检查表中是否存在Range1中的值，并返回表中匹配值的位置。
• ISNUMBER()通过检查MATCH()函数是否返回一个数字来检查是否找到匹配。
• 使用Range2函数将其与SUMPRODUCT()强制矩阵计算相乘

编辑：

这对于一个非常有限的样本是有效的。当我将第四行添加到我的数据中时，公式就停止了预期的工作。见截图：

​

它正确地将前两个值放入和中，第四个值未考虑在内。