为 SUMIF 函数选择命名范围的特定列 [英] Selecting a Specific Column of a Named Range for the SUMIF Function

问题描述

我正在尝试创建一个 SUMIF 函数，该函数动态地将 Excel 工作表中指定范围的特定列中的值相加.

I am trying to create a SUMIF function that dynamically adds up values in a specific column of a named range in my Excel sheet.

当没有命名范围时，很容易做到这一点:

It is very easy to do this when there is no named range :

该公式会挑选出所有名称中包含London"的单元格，并汇总与伦敦相关的费用.

The formula picks out all the cells that contain "London" in their name and sums up the expenses related to London.

我想要做的是使用名为 TripsData (A2:B5) 的命名范围，并告诉 SUMIF 函数对该范围的第 2 列中满足名称中包含伦敦的标准的条目求和.

What I am trying to do is to use a named range called TripsData (A2:B5) and tell the SUMIF function to sum the entries in the column 2 of this range that meet the criterion of having London in their name.

如何在不需要为第 2 列创建第二个命名范围的情况下完成这项工作，只需告诉 Excel 在此命名范围的指定列内查找?索引/匹配仅返回一个值，因此当有多个单元格名称中包含 London 时，该值不起作用.

How can I make this work without needing to create a second named range for column 2 and simply by telling Excel to look within the specified column of this named range? Index/Match only return one value so that doesn't work when there are several cells with London in their name.

感谢您的帮助！

推荐答案

使用 INDEX 引用指定范围内的特定列(可以引用整列)，像这样

Use INDEX to refer to a specific column in the named range (it can refer to a whole column), like this

=SUMIF(TripsData,"*London*",INDEX(TripsData,,2))

这篇关于为 SUMIF 函数选择命名范围的特定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！