为 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屋!