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