☰

如果相应行满足条件，则按范围计算总金额

tag-icon • tag-icon microsoft-excel worksheet-function
如果相应行满足条件，则按范围计算总金额

我正在尝试创建一个电子表格，根据相应的行计算每个月的小时数总和。为了使示例更清晰一些，对于下面的 Excel，我应该为客户 1 获取 28 的值，为客户 3 获取 16 的值。

我正在尝试使用 SUMIF 函数，但总和范围不能位于多个单元格上。

在此处输入图片描述

我希望看到以下信息：

在此处输入图片描述

答案1

您可以创建一个简单的公式：

=SUM(IF(B5:B21="Client 1",G5:AK21))

这是通过两个步骤进行数组计算：

  1. 如果 B 列 =“客户 1”，则返回整行数字（甚至空白）。
  2. 一旦我获得了所有行（其中大多数包含空白），您只需将这些值相加即可。

如果需要，您可以"Client 1"在公式中用对外部单元格的引用进行替换。另外，请确保公式第二部分中的整个月份范围都是准确的G5:AK21。

答案2

借鉴=SUM(IF())​@戴夫可以使用OFFSET函数来选择与一年 12 个月中的每个月相对应的列。

该OFFSET函数通过其高度和宽度定义一个矩形范围，范围的左上角根据指定工作表单元格下方的行数和右侧的列数定义。在下面的方法中，我使用以黄色突出显示的单元格作为指定单元格（单元格E17）。

范围A2:E14（见下面的屏幕截图）提供了一些信息，或者元数据，每年 12 个月的每日值列的位置与突出显示的单元格相对应。有用的值位于标题为列偏移和列例如，一月份的每日值从黄色单元格右侧第 1 列开始，占据 31 列；二月份的每日值从黄色单元格右侧第 32 列开始，占据 29 列（如果将单元格中的年份更改为非闰年，B1单元格中的值将更改为E428），等等。月＃和累计是计算所需的中间值列偏移和列。

元数据构建

按月和客户汇总数据总数的公式可以按细分格式编写为

=SUM(
IF(Sheet1!$B$18:$B$23=$A2,
OFFSET(Sheet1!$E$17,
1,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),
6,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4)
)
)
)

或者，作为一个完整的公式

=SUM(IF(Sheet1!$B$18:$B$23=$A2,OFFSET(Sheet1!$E$17,1,VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),6,VLOOKUP(B$1,Sheet1!$A$3:$E$14,5))))

其中假定元数据和每日数据包含在工作表中Sheet1。

此公式适用于 1 月/客户 1，可以复制到其他月份和客户的其余单元格，如下面的屏幕截图中部分显示。确保元数据和结果表中使用的月份名称完全匹配，以避免#N/A结果出现错误。

构建结果表

该函数的第二个和第四个参数OFFSET分别为 1 和 6，因为数据从黄色突出显示的单元格后的第一行开始，在所示的示例中，有 6 行数据。如果行数不同，只需替换正确的值即可。

第三和第五个参数只是使用函数VLOOKUP来查找列偏移和列所涉及月份的元数据中的值。

相关内容