为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 :
该公式将选择名称中包含伦敦"的所有单元格,并汇总与伦敦相关的费用.
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在该命名范围的指定列中进行查找?索引/匹配仅返回一个值,因此当有多个名称为伦敦的单元格时,该值将不起作用.
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屋!